Update all in one query...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there everyone! Using A02 on XP. Not a programmer but love all the help
I get in the newsgroups.

I have an imported table with a Number field (Field Size - Double) called
[Hours] that contains a 0, 1 or 2 (it may also be null).

I need to run an update query that will change the 0's to 499, 1's to 501
and 2's to 1000. Lastly, the nulls should become zeros (don't want them to
be 499's!).

I can write 4 update queries but I think maybe I should do a Case or Switch
statement. Not sure.

Could someone tell me what would be the best way to do this? Thanks in
advance for any help or advice!
 
You might be able to use the Choose function since your starting values are
sequential (except for the null).

STEP 1: BACKUP your data, there is no undo on bulk changes.

UPDATE [YourTableName]
SET [Hours] = Choose(Nz([Hours],3)+1,499,501,1000,0)

Choose selects the Nth item based on the first argument. Nz(returns 3 if
Hour is Null, so that becomes the fourth argument.)
 
DubYa, you da Bomb! I tried an If but got myself confused. You nested it
beautifully! Works GREAT! Thanks very much for your time!
--
Bonnie


Robert_DubYa said:
Use the IIF function:

iif([Hours] = 0,499,iif ([Hours] = 1,501,iif([Hours] = 2,1000,0)))



Bonnie said:
Hi there everyone! Using A02 on XP. Not a programmer but love all the help
I get in the newsgroups.

I have an imported table with a Number field (Field Size - Double) called
[Hours] that contains a 0, 1 or 2 (it may also be null).

I need to run an update query that will change the 0's to 499, 1's to 501
and 2's to 1000. Lastly, the nulls should become zeros (don't want them to
be 499's!).

I can write 4 update queries but I think maybe I should do a Case or Switch
statement. Not sure.

Could someone tell me what would be the best way to do this? Thanks in
advance for any help or advice!
Click to expand...
Click to expand...
 
John, Thanks bunches for your function. Love it but can't use it here as
someone may submit data that doesn't follow the sequence. I'll keep it for
future reference though cause it's cool!
--
Bonnie


John Spencer said:
You might be able to use the Choose function since your starting values are
sequential (except for the null).

STEP 1: BACKUP your data, there is no undo on bulk changes.

UPDATE [YourTableName]
SET [Hours] = Choose(Nz([Hours],3)+1,499,501,1000,0)

Choose selects the Nth item based on the first argument. Nz(returns 3 if
Hour is Null, so that becomes the fourth argument.)

Bonnie said:
Hi there everyone! Using A02 on XP. Not a programmer but love all the
help
I get in the newsgroups.

I have an imported table with a Number field (Field Size - Double) called
[Hours] that contains a 0, 1 or 2 (it may also be null).

I need to run an update query that will change the 0's to 499, 1's to 501
and 2's to 1000. Lastly, the nulls should become zeros (don't want them
to
be 499's!).

I can write 4 update queries but I think maybe I should do a Case or
Switch
statement. Not sure.

Could someone tell me what would be the best way to do this? Thanks in
advance for any help or advice!
Click to expand...
Click to expand...
 
Back
Top