Variable Fields Update in Parameterised Query

[Bob Phillips]

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?

[SamT]

I've never done Access so take this with a whole block of salt

MyArr = Query Entire Record
MyArr(n) = Date
Update Entire Record = MyArr

[Bob Phillips]

I've never done Access so take this with a whole block of salt

MyArr = Query Entire Record
MyArr(n) = Date
Update Entire Record = MyArr

The whole point is to handle the variability via the SQL, application code is not the approach I want to take.

[Bob Phillips]

I asked this question on ListServ as people here seemed no more aware of how to do than I was, and I got a response in 5 minutes from the super-smart Rory Archibald. As he pointed out, I was well over-egging it, I more or less had it with the IIF solution but I added in a complication that stopped it working.

This is the working solution


PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
UPDATE Account
SET DateOpened = IIF(param.date.opened IS NULL, DateOpened, param.date.opened),
DateClosed = IIF(param.date.closed IS NULL, DateClosed, param.date.closed)
WHERE Id = param.id;

[Aussiebear]

So Bob the change was only in the "Is Not Null" to "Is Null"?

[Bob Phillips]

No Ted, it was the second code sample, where I had a separate SELECT to try and get the present value, and all I had to do was use that field value. In pseudo code for one column


FieldValue = IF param.for.that.field IS NULL Then FieldValue Else param.for.that.field ENDiF

or as an actual column


DateOpened = IIF(param.date.opened IS NULL, DateOpened, param.date.opened)

[Aflatoon]

the super-smart Rory Archibald

There you go over-egging it again. :)