update two fields at once

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?


0
John
1/23/2010 6:35:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
897 Views

Similar Articles

[PageSpeed] 24

On Sat, 23 Jan 2010 13:35:02 -0500, "John MilburySteen"
<j.milbury@comcast.net> wrote:

>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.
-- 

             John W. Vinson [MVP]

0
John
1/23/2010 8:50:32 PM
Reply:

Similar Artilces: