Changing table values based on IF statement

[tylerpickering]

Hi guys--

Total Access newb here, I have a database that has an "Origin Country" field and need to standardize the names...ex. Canada needs to be CAN, but are currently CA and CN.



Here is what I came up with so far:

OCountry: IIf([Origin Country]="CA","CAN",iif([Origin Country]="CN","CAN",iif([Origin Country]="MX", "MEX",iif([Origin Country]="ME", "MEX",[Origin Country])))

However, it does not create the new country code as I had hope. Not sure if OR statements are possible like in excel (obviously making this much simpler), but like I said, big time n00b hoping to learn.

[JoeM]

I think the easiest thing to do would be to create a lookup table of all your different countries, and simply link your table to it and return the right value, i.e.

LookupValue   ValueToReturn
CA                CAN
CAN               CAN
CN                CAN
ME                MEX
MEX               MEX
MX                MEX

So, you would just do a Left Join from your main data table on the to this table, joining on the LookupValue field, and returning the ValueToReturn field.

The beauty of this is you can easily add other countries and/or abbreviations to this data table in the future (as the need arises), and not have to change any of your formulas in your queries!

[June7]

Yes, lookup table is best, especially if you have a lot of countries and/or want to allow adding countries.

However, don't understand "it does not create the new country code". The expression should work. If you want to actually change the value in the field, that requires an UPDATE action SQL.

Don't you want to use the international 2-letter code for addressing? Or there is a 3-letter standard http://www.unc.edu/~rowlett/units/codes/country.htm

[tylerpickering]

Thanks to you both for responding! I'll create a table and see how it goes--thanks again!

[tylerpickering]

So, I'm not sure how to do this join...could you help me out with the query language?

Here's what I have

Origin Fixed: : FROM [Country Codes] Left JOIN [All Columns] ON [All Columns].[Origin Country] to [Country Codes].ValueToReturn

[JoeM]

Easiest way is to add both tables to Query View, drag a line from the "Origin Country" code field in the main table to the matching field in your lookup table.
Then, double-click the line you just made by joining this two fields and select the second radio button option listed there.

Then, simply select the fields you want to return from the tables.

[tylerpickering]

Ah got it, much easier...thanks!