On Sat, 23 Jan 2010 13:35:02 -0500, "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?
A tricky but simple solution is to update F1 to
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
John W. Vinson [MVP]