Warren Phillips wrote:

I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I
want to evaluate a field called "Cost Type" for the following values and then
populate a new field (E1) with a value based on the Cost Type.

For example

If Cost Type = L, populate E1 with 1405
If Cost Type = M, populate E1 with 1410
If Cost Type = S, populate E1 with 1480
If Cost Type = G, populate E1 with 1495

Then.....I want to evaluate a different field (Pay Item) for the following
conditions and place a static value in the E1 field...and..these values a

If Pay Item = 955000, populate E1 with 1490
If Pay Item = 955300, populate E1 with 1491
If Pay Item = 955200, populate E1 with 1492
If Pay Item = 955100, populate E1 with 1493

Is there any way to combine these conditions (evalutating 2 fields) into 1
statement


You could use the Switch function, which is less confusing
than a bunch of nested IIf functions:

Switch([Cost Type] = L,1405, [Cost Type] = M,1410, ...) &
Switch([Pay Item] = 955000,1490, ...) As E1

BUT, it is not a good idea to include data values such as L,
M. etc. and 1405, 1410, etc. anywhere except in a table.

It would be better if you had a table with an primary key
field for the L, M, etc and another field for the 1405,
1410. etc. THen you can join this table to your data table
in the query to translate the L to 1405 automatically,
Similarly for the pay items, either in a third table or
combined into the second table.
--
Marsh
MVP [MS Access]