update two fields at once
in [Queries]
|
Prev: FUNCTION PREDICTING EVEN/ODD COUNT IN QUERY
Next: Query input value to determine another field results
From: John MilburySteen on 23 Jan 2010 13:35 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?
From: John W. Vinson on 23 Jan 2010 15:50
On Sat, 23 Jan 2010 13:35:02 -0500, "John MilburySteen" <j.milbury(a)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] |