If Statements (Nested??)

[Warren Phillips]

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 are:

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

Thank you

Warren Phillips

 

[KARL DEWEY]

Which gets pripority? If both IIFs are satisfied then which do you put in E1?

 

[Warren Phillips]

That will never happen. The values in both fields and the values I am
wanting to populate in the new E1 field will always be unique for any given
record. There will be multiple records that have a Cost Type of L or a Cost
Type of M, etc.....or.....a Pay Item of 955000 or a Pay Item of
955300......but each record will contain one or the other. In other words,
there would never be a Cost Type of L AND a Pay Item of 955000 in the same
record, etc.....

Thank you Karl!!!!

 

[KARL DEWEY]

Try this. Create a table --
tblConvert --
Cost Type - text
Pay Item - number
E1 - text

Populate like this --
Cost Type Pay Item E1
L 1405
M 1410
S 1480
G 1495
955000 1490
955300 1491
955200 1492
955100 1493

SELECT YourTable.[Cost Type], YourTable.[Pay Item], IIf([tblConvert].[Cost
Type] Is Not Null,[tblConvert].[E1],IIf([tblConvert_1].[Pay Item] Is Not
Null,[tblConvert_1].[E1],Null)) AS E1
FROM (YourTable LEFT JOIN tblConvert ON YourTable.[Cost Type] =
tblConvert.[Cost Type]) LEFT JOIN tblConvert AS tblConvert_1 ON
YourTable.[Pay Item] = tblConvert_1.[Pay Item];

 

[rbeach]

You could use the formula below. This if the field are text. If they are
numbers you will need to remove the quotes.

E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost
Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] =
"955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] =
"955200","1492",Iif([Pay Item] = "955100","1493","")

 

[Warren Phillips]

Rick

This could not have worked out any better. It worked out perfectly. Thanks
to everyone for taking the time to help.

You could use the formula below. This if the field are text. If they are
numbers you will need to remove the quotes.

E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost
Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] =
"955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] =
"955200","1492",Iif([Pay Item] = "955100","1493","")
--
Rick

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 are:

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

Thank you

Warren Phillips

 

[Duane Hookom]

I think it could have worked out a lot better with Karl and Marsh's
suggestion to use data stored in tables rather than hard coding values into
an expression. What happens to this solution when Cost Types are changed?
You shouldn't build expressions that might require maintenance in the
future.

--
Duane Hookom
MS Access MVP

Rick

This could not have worked out any better. It worked out perfectly.
Thanks
to everyone for taking the time to help.

You could use the formula below. This if the field are text. If they are
numbers you will need to remove the quotes.

E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost
Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] =
"955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] =
"955200","1492",Iif([Pay Item] = "955100","1493","")
--
Rick

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
are:

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

Thank you

Warren Phillips