Help with update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I need a little help with formulating an update query.

This is my query statement:

UPDATE tbl_Empl_Master
SET tbl_Empl_Master.S3BadgeExpDate =
(Select SSCEmployees.Expdate
FROM SSCEmployees
Where SSCEmployees.S3ID = tbl_Empl_Master.S3ID);

When I execute this query, Access tells me:
Action must use an updateable query.

What I am doing wrong?

Thanks,
Rich
 
rich said:
I need a little help with formulating an update query.

This is my query statement:

UPDATE tbl_Empl_Master
SET tbl_Empl_Master.S3BadgeExpDate =
(Select SSCEmployees.Expdate
FROM SSCEmployees
Where SSCEmployees.S3ID = tbl_Empl_Master.S3ID);

When I execute this query, Access tells me:
Action must use an updateable query.
Click to expand...


Don't ask me why, but Update queries don't like subqueries.
Use the DLookup function instead:

UPDATE tbl_Empl_Master
SET tbl_Empl_Master.S3BadgeExpDate =
DLookup("Expdate", "SSCEmployees", "S3ID = " & S3ID)
 
Thanks for the reply Marsh.

I used the query just as you provided and now I am getting prompted for:
tbl_Empl_master.S3BadgeExpDate which is the destination table/column.

Note sure why. Hmmmm.....
 
Marshall Barton said:
Don't ask me why, but Update queries don't like subqueries.
Use the DLookup function instead:

UPDATE tbl_Empl_Master
SET tbl_Empl_Master.S3BadgeExpDate =
DLookup("Expdate", "SSCEmployees", "S3ID = " & S3ID)
Click to expand...
 
In the future, please post a Copy/Paste of the query's SQL
view so I can see exactly what you used.

When a query prompts for a value, it means that the prompt
could not be found in the query's table(s). From that I
conclude that the field name in the table is different from
S3BadgeExpDate or you made some other change to the query
(e.g. somehow it became
SET [tbl_Empl_Master.S3BadgeExpDate]=

BTW, there is no need to use the table name here. It could
be just:
SET S3BadgeExpDate=

Are you sure you want to update every record in the table?
 
Back
Top