JLott JLott - 17 days ago 5
SQL Question

Doing Multiple Updates in Access at the same time

I have very little knowledge of how access works, but I need some more efficient then what I am doing now.

I have these queries:

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #04/07/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#4/7/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #2/27/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#2/27/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] =#5/29/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#5/29/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] =#8/25/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#8/25/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/28/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#8/28/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #9/29/2003#
WHERE ((([Receipt Audit].[Receipt Date])=#9/29/303#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #2/25/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#2/25/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #3/30/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#3/30/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/23/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/23/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/25/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/25/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/26/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/26/404#));


UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/27/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/27/404#));

UPDATE [Receipt Audit]
SET [Receipt Audit].[Receipt Date] = #8/30/2004#
WHERE ((([Receipt Audit].[Receipt Date])=#8/30/404#));


The problem is I have to run them all individually. Is there a way I could combine them into one query? Any help is appreciated.

Answer

Perhaps something like this?

UPDATE [Receipt Audit]
SET [Receipt Date] = DateSerial(Switch(Year([Receipt Date])=303,2003,Year([Receipt Date])=404,2004,True,Year([Receipt Date])),Month([Receipt Date]),Day([Receipt Date]))
WHERE Year([Receipt Date]) IN (303,404)