I have an Access database with an Excel front-end, and I can retrieve, insert, update, delete rows from the Access tables using data in Excel tables.

I have it all working fine, but at present, the update query is written such that it actions all columns in the table. So for instance, in my Account table, I have to update the Date Opened, Date Closed, Account Number, etc., even though the only field changed is the date closed. I know I could write separate queries for each field, but this would mean adding a ton of extra code to the front-end, and it justs seems naff. I want one query that can update 1, 2, 3 or all fields depending upon what I supply. I can do this easily in SQL Server, but how do I do it with Access SQL.

So I thought I would use a query that determined if a parameter was present at run time and update if this was the case.

My first thought was an iif statement, like so

PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
UPDATE Account 
SET DateOpened = IIF(param.date.opened IS NOT NULL, param.date.opened), 
    DateClosed = IIF(param.date.closed IS NOT NULL,  param.date.closed)
WHERE Id = param.id;
This failed when one of the parameters was null, it set that date to null, not what I intended.


Second attempt was to get the current value if the parameter was null, as in

PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
UPDATE Account 
SET DateOpened = IIF(param.date.opened IS NULL, (SELECT ac.DateOpened From Account AS ac WHERE Id = param.id), param.date.opened), 
    DateClosed = IIF(param.date.closed IS NULL, (SELECT ac.DateClosed From Account AS ac WHERE Id = param.id), param.date.closed)
WHERE Id = param.id;
This failed at runtime saying the 'Operation must be an updateable query'?


I Googled this for help and saw an idea where the query had separate update statements for each field, so I tried this

PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;

UPDATE Account 
SET DateOpened = param.date.opened 
WHERE Id = param.id AND param.date.opened IS NOT NULL;

UPDATE Account 
SET DateClosed = param.date.closed 
WHERE Id = param.id AND param.date.closed IS NOT NULL;
but I got a compile error with 'Characters found after end of UPDATE statement', with the second UPDATE highlighted. Removing the semi-colon at the end of the first update created a syntax error in the first update statement.


The first version is more elegant in my view, but any working version would be better than what I have at present. Anyone know what the query would look like to achieve this objective?