Update Query

[Laurie]

need help with the syntax for an update query to update a blank field named
PayCode (created because of the way the source financials are) in my access
table with the value of the source financials field (there are 6 paycode
fields tied to each of their reserve buckets) if the source paycode field
is not null.
Example
Paycode paycodea paycodeb paycodec paycoded

if paycodea is not null (values are 1-999), update the value in paycodea to
paycode field.

 

[John W. Vinson]

need help with the syntax for an update query to update a blank field named
PayCode (created because of the way the source financials are) in my access
table with the value of the source financials field (there are 6 paycode
fields tied to each of their reserve buckets) if the source paycode field
is not null.
Example
Paycode paycodea paycodeb paycodec paycoded

if paycodea is not null (values are 1-999), update the value in paycodea to
paycode field.

Your two paragraphs are contradictory.

First you say "update a blank field named Paycode if the source paycode field
is not null".

Then you say "update the value in paycodea to paycode field".

Which field do you want to change? Paycode, or paycodea?

What role (if any) do paycodeb, c and d play?

 

[Laurie]

sorry for the confusion....I need to update paycode with the value contained
in paycodea, or paycodeb, or paycodec, or paycoded, or paycodee or paycodef.
Each of the paycode fields (a-f) could have a value of 1-999.

 

[John Spencer]

Still a little confusion. I assume you want PayCode to contain the value from
the first (alphabetically) field that does have a value.

The SQL to do that would possibly look like the following:
Update SomeTable
SET PayCode =
NZ([PayCodeA],Nz([PaycodeB],Nz([PayCodeC],Nz([PaycodeD]),Nz([PayCodeE],[PaycodeF])))))
WHERE PayCode Is Null

If you are using query design view you would put

NZ([PayCodeA],Nz([PaycodeB],Nz([PayCodeC],Nz([PaycodeD]),Nz([PayCodeE],[PaycodeF])))))

in the UPDATE To "cell" under PayCode

If you have a lot of records to update you might be better off running 6
update queries, where you update Paycode if it is null to each of the other
fields in sequence.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Update SomeTable
SET PayCode =
NZ([PayCodeA],Nz([PaycodeB],Nz([PayCodeC],Nz([PaycodeD]),Nz([PayCodeE],[PaycodeF])))))
WHERE PayCode Is Null

Thanks John... glad you posted, I wouldn't have done anything as elegant as
that!