Update Field Based on other Other Fields

[tcgaines]

drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.

if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"

Right now I'm getting a circular reference error :/

please advise. Thank you.

 

[tcgaines]

thanks pat. this is in SQL

access query design view, drvRegion:
iif([drvOrderSource="NL_MIRROR",[drvRegion]="EUROPE",0]

This causes a circular reference error.

But if it did work, I don't like that 0... If drvRegion is already populated with a region, I don't want it to change.

thanks.

 

[tcgaines]

iif([drvOrderSource] = "NL_MIRROR", "EUROPE",iif([drvOrderSource] = "something else", "AMERICA", iif([drvOrderSource] = "something else", "ASIA", Null]))) AS [drvRegion]

this works great pat, except for one *small thing*

This is how it works.

drvRegion may already be populated. If it is not, then drvOrderSource determines the region, and this query updates those records. However, this query also wipes drvRegion where it was once populated.

If drvRegion is not null, dont do anything with it.
if drvRegion is null, populate it using the criteria above.

Does that make sense?

Thanks.

 

[tcgaines]

iif([drvOrderSource] = "NL_MIRROR", "EUROPE",iif([drvOrderSource] = "something else", "AMERICA", iif([drvOrderSource] = "something else", "ASIA", Null]))) AS [drvRegion]

Solution:
iif([drvOrderSource] = "NL_MIRROR", "EUROPE",iif([drvOrderSource] = "something else", "AMERICA", iif([drvOrderSource] = "something else", "ASIA", drvRegion))) AS [Region]

This also took care of the circular reference error...

Thanks!!