update two fields at once

[John MilburySteen]

Hi Access Gurus,

In A2003 I want to write an update query which updates about ten fields at
once. The idea is that if any one of these fields is null, I update it with
a value read from a linked Excel table.

For the sake of simplicity, let's say I have only 2 fields, F1 and F2.
Either one of them might have a null value. The logic is: IF F1 is null,
update it, and IF F2 is null, update it, but if the field already has a
value (is non-null), let it alone. Can I do this in one update query, or do
I have to write a separate query for each field I am updating? In the query
pane (I do not do visual Basic), I would like to be able to write for the
Update To slot:

IIF(IsNull(F1), ExcelTable.F1, Let ThisField Alone)
IIF(IsNull(F2), ExcelTable.F2, Let ThisField Alone)

but, of course, I do not have a token such as LetThisFieldAlone. I guess I
am trying to finesse a conditional update. Is something like this possible?
Or should I just bite the bullet and write 10 separate update queries, each
one simple, but, when executed one by one, very slow?

 

[John W. Vinson]

Hi Access Gurus,

In A2003 I want to write an update query which updates about ten fields at
once. The idea is that if any one of these fields is null, I update it with
a value read from a linked Excel table.

For the sake of simplicity, let's say I have only 2 fields, F1 and F2.
Either one of them might have a null value. The logic is: IF F1 is null,
update it, and IF F2 is null, update it, but if the field already has a
value (is non-null), let it alone. Can I do this in one update query, or do
I have to write a separate query for each field I am updating? In the query
pane (I do not do visual Basic), I would like to be able to write for the
Update To slot:

IIF(IsNull(F1), ExcelTable.F1, Let ThisField Alone)
IIF(IsNull(F2), ExcelTable.F2, Let ThisField Alone)

but, of course, I do not have a token such as LetThisFieldAlone. I guess I
am trying to finesse a conditional update. Is something like this possible?
Or should I just bite the bullet and write 10 separate update queries, each
one simple, but, when executed one by one, very slow?

A tricky but simple solution is to update F1 to

NZ([AccessTable].[F1], [ExcelTable].[F1])

and the same for the other fields. If the Access table field is not NULL, the
NZ (Null To Zero) function will return it (updating the field to itself, a
do-nothing operation); if it is NULL it will pull the value from the Excel
table.