If then statement??

[Schon]

I am new to Access and am trying to take a field and change the value
depending on what the current value is. example

If [field 1] is 01 then "supplies", if [field 1] is 02 then "merchandise",
if [field 1] is 03 then "equipment", else "blank"

can i do this in a query for a field or do i need to do it in a make table
query then grab that field from there?? Thanks

 

[KARL DEWEY]

Try this --
IIF([field 1] = "01", "supplies", IIF([field 1] = "02", "merchandise",
IIF([field 1] = "03", "equipment", "")))

 

[John Spencer]

You can use a calculated field.

Field: IIF([Field 1] ="01","Supplies",IIF([Field 1] ="02","Merchandise",
IIF([Field 1] ="03","Equipment",Null)))

It would be better to build a small table with the two fields - ItemTypeCode
and ItemTypeDescription. Then add this new table your existing query and join
ItemTypeCode to the existing field. Double-click on the join line and select
the option to give you ALL records in the existing table and only matches from
the new table.

One BIG advantage to this is that if you add another code to Field 1 "04",
then all you need to do is to add one new record to the new table and it will
automatically carry through to any existing queries, reports, forms, etc.
without you needing to do any additional work.

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