If Statements (Nested??)

W

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
 
W

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!!!!

:)
 
K

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];
 
R

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","")
 
M

Marshall Barton

Warren said:
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
Click to expand...


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.
 
W

Warren Phillips

Rick

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

rbeach said:
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


Warren Phillips said:
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
Click to expand...
Click to expand...
 
D

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


Warren Phillips said:
Rick

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

rbeach said:
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


Warren Phillips said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Basic IF/AND and Nested IF/AND Statements? 2
Subject: Need help on Iff Statement 1
SQL Statement in Access 2
Nested IIf Statements 5
IIf, then..else - nesting three statements 3
Why does this Nested IIF statement add AS fields 11
query to textbox 1
Need help on Iff Statement 1

Top