Update Query asks for parameter rather than using specified database table value

Question

Table LoadDates has two date fields, DocDate and SpreadSheetDate, never more than one record each. Both date values are populated. Table DataFile has date field AsOf.

I want to set the DataFile.AsOf values to the value in LoadDates.SpreadSheetDate where ever the DataFile.AsOf value is null.

I created this Update Query:

UPDATE DataFile SET DataFile.AsOf = LoadDates.SpreadSheetDate

WHERE DataFile.AsOf is null;

When I execute the query, Access displays an Enter Parameter requester for inputting a date instead of just using the LoadDates.SpreadSheetDate value. How can I get it to just use the LoadDates value?

Answer 1

All DataFile.AsOf fields are getting the correct value from LoadDates.SpreadSheetDate. LoadDates table is not being updated. No parameter requester appears. All's well!

Answer 2

That would also update the date fields in that table. However, are all of the dates being updated to the correct value?

Answer 3

I've solved the issue.

This update works with no parameter requester:

UPDATE DataFile, LoadDates SET DataFile.AsOf = LoadDates.SpreadSheetDate

WHERE ((DataFile.AsOf) is null);

Answer 4

When Access pops up a parameter input box, it means it can't find the item referenced so it assumes that the user should be prompted for the value. The reason it can't find it could be because of a misspelling or the referenced object is not in the scope of the query.

As George pointed out, the latter is your issue.

Answer 5

You have to join the LoadDates table into the query to use it.

Which field is the same in both tables?

It would have to be something like this, for example:

UPDATE DataFile INNER JOIN DataFile ON DataFile.YourIDFieldNameGoesHere = LoadDates.YourIDFieldNameGoesHere

SET DataFile.AsOf = LoadDates.SpreadSheetDate

Or you could use DLookup perhaps.