Problem when changing field names

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I did not build the entire database I'm trying to figure
out, so forgive me if I don't really know what's going on
with it.

We have two fields which are confusing in our database:
"FloorShipDate" and "FloorShippedDate". The first being
the date the floor is SCHEDULED to be shipped, the second
being the date it actually IS shipped. I wanted to clear
up this confusion by simply changing the field name in
the table for the first field to
be "FloorScheduledShipDate". However, when I do this, a
report built on a query built on this table brings up the
error "Orders.FloorShipDate" when we try to open it. This
query seems to be built in SQL form and I can't get it to
show me the form I'm more familiar with. I changed all
places in the SQL where it shows "FloorShipDate" to
read "FloorScheduledShipDate" and it still gives me this
error. This is a linked query (I really don't know what
else uses it), and I can't figure out how to change it.

What am I missing? Do I need to try to sort out the
relationships in this database in order to fix this
problem?

Thanks in advance,
Melissa
 
Changing the name of the field in the table will not automatically update
all queries, forms, reports, etc., especially if you have code that uses
those field names. And, changing the name can create all kinds of "fun", as
you're seeing.

It may be easier to just change the name that is being displayed on a report
instead of changing the name of the table's field.

If your report is using an SQL statement as the recordsource, this is easily
done by changing a small part of the SQL statement. Assuming that the SQL
statement has Orders.FloorShipDate as part of the SELECT clause, replace it
with this:
Orders.FloorShipDate AS FloorScheduledShipDate

That will change what is displayed as the field name on the report. You then
need to change the control source for the control that is bound to
Orders.FloorShipDate to FloorScheduledShipDate.
 
Open the report in design view and check the sorting and grouping dialog.
It's possible that the FloorShipDate is referenced there.

Also check the control sources on the report - one of them may still be
bound to FloorShipDate.
 
What the query actually has is this:
SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate],"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]

What I want to change is the field of FloorShipDate. Can
I just replace it in the square bracket as you have it?
The report itself shows a list of jobs that are scheduled
to go out on a certain day, so I don't need to change
anything in there, I think.

Thanks,
Melissa
 
Have done so, and it seems to be a problem with the query
rather than the report. And all the forms automatically
updated and have been working fine. It just seems to be
*this* query. Other queries which were built in the
Design View don't have this problem. But I can't get this
query to give me Design View (is it because it's a Union
Query??), so I can't figure out how to solve this problem.

Thanks though,
Melissa
 
See my reply to Ken's post.

Thanks.
Melissa
-----Original Message-----
UNION queries can only be displayed in SQL view. Post the SQL.

--
Joan Wild
Microsoft Access MVP




.
Click to expand...
 
Try this:

SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate] AS FloorScheduledShipDate,
"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]
--
Ken Snell
<MS ACCESS MVP>


Melissa said:
What the query actually has is this:
SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate],"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]

What I want to change is the field of FloorShipDate. Can
I just replace it in the square bracket as you have it?
The report itself shows a list of jobs that are scheduled
to go out on a certain day, so I don't need to change
anything in there, I think.

Thanks,
Melissa
If your report is using an SQL statement as the recordsource, this is easily
done by changing a small part of the SQL statement. Assuming that the SQL
statement has Orders.FloorShipDate as part of the SELECT clause, replace it
with this:
Orders.FloorShipDate AS FloorScheduledShipDate

That will change what is displayed as the field name on the report. You then
need to change the control source for the control that is bound to
Orders.FloorShipDate to FloorScheduledShipDate.
Click to expand...
Click to expand...
 
Grr.

Now when I try to open the query to see if that fixed the
problem, it gives me a little box:
"Enter Parameter Value
FloorShipDate
OK Cancel"

What's the next suggestion?
Thanks,
Melissa

-----Original Message-----
Try this:

SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate] AS FloorScheduledShipDate,
"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]
--
Ken Snell
<MS ACCESS MVP>


Melissa said:
What the query actually has is this:
SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate],"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]

What I want to change is the field of FloorShipDate. Can
I just replace it in the square bracket as you have it?
The report itself shows a list of jobs that are scheduled
to go out on a certain day, so I don't need to change
anything in there, I think.

Thanks,
Melissa
If your report is using an SQL statement as the recordsource, this is easily
done by changing a small part of the SQL statement. Assuming that the SQL
statement has Orders.FloorShipDate as part of the
Click to expand...
SELECT
clause, replace it
with this:
Orders.FloorShipDate AS FloorScheduledShipDate

That will change what is displayed as the field name
Click to expand...
on
the report. You then
need to change the control source for the control that is bound to
Orders.FloorShipDate to FloorScheduledShipDate.
Click to expand...
Click to expand...


.
Click to expand...
 
? The appearance of that parameter box suggests that the FloorShipDate field
is not in the table (or is it a query?) Floor Delivery Query ....

Check the Floor Delivery Query to see if the field is in it. Did you by
chance change it earlier and it's now the new name?

--
Ken Snell
<MS ACCESS MVP>
Melissa said:
Grr.

Now when I try to open the query to see if that fixed the
problem, it gives me a little box:
"Enter Parameter Value
FloorShipDate
OK Cancel"

What's the next suggestion?
Thanks,
Melissa

-----Original Message-----
Try this:

SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate] AS FloorScheduledShipDate,
"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]
--
Ken Snell
<MS ACCESS MVP>


Melissa said:
What the query actually has is this:
SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate],"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]

What I want to change is the field of FloorShipDate. Can
I just replace it in the square bracket as you have it?
The report itself shows a list of jobs that are scheduled
to go out on a certain day, so I don't need to change
anything in there, I think.

Thanks,
Melissa

If your report is using an SQL statement as the
recordsource, this is easily
done by changing a small part of the SQL statement.
Assuming that the SQL
statement has Orders.FloorShipDate as part of the SELECT
clause, replace it
with this:
Orders.FloorShipDate AS FloorScheduledShipDate

That will change what is displayed as the field name on
the report. You then
need to change the control source for the control that
is bound to
Orders.FloorShipDate to FloorScheduledShipDate.
Click to expand...


.
Click to expand...
Click to expand...
 
Melissa,

Check the Floor Delivery Query. Does it have a FloorShipDate in it or does it
have a FloorShippedDate in it?

Since the query you posted has FloorShipDate the the Floor Delivery Query should
have _that_ field in it or you need to change the current query to use the
FloorShippedDate vice the FloorShipDate.

Sorry to butt in here, but that is one thing I noticed.

Ken said:
Try this:

SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate] AS FloorScheduledShipDate,
"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]
--
Ken Snell
<MS ACCESS MVP>

Melissa said:
What the query actually has is this:
SELECT [JobNumber], [OrderID], [Customer], [JobName],
[JobAddress], [JobCity], [FloorShipDate],"Floor" AS Type,
[FloorShipNotes],[FloorBuilt],[FloorFinalDone]
FROM [Floor Delivery Query]

What I want to change is the field of FloorShipDate. Can
I just replace it in the square bracket as you have it?
The report itself shows a list of jobs that are scheduled
to go out on a certain day, so I don't need to change
anything in there, I think.

Thanks,
Melissa
If your report is using an SQL statement as the recordsource, this is easily
done by changing a small part of the SQL statement. Assuming that the SQL
statement has Orders.FloorShipDate as part of the SELECT clause, replace it
with this:
Orders.FloorShipDate AS FloorScheduledShipDate

That will change what is displayed as the field name on the report. You then
need to change the control source for the control that is bound to
Orders.FloorShipDate to FloorScheduledShipDate.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top