Expression Help

R

rciolkosz

I can make this expression work in my query but how do I add to the
expression to make the rest work.

IIf([# of bags]=1,"A")

How do I add if =2,"B"

Etc, Etc
 
G

golfinray

An IIF statement is IIF([field]=whatever,what to do if true, what to do if
not true)
So if the only possibilities are 1 and 2 you could use:
Iff([field]=1,"A","B")
 
R

rciolkosz

So I cannot make it work like, if "A" then 1, if "B" then 2 all in the same
field?

golfinray said:
An IIF statement is IIF([field]=whatever,what to do if true, what to do if
not true)
So if the only possibilities are 1 and 2 you could use:
Iff([field]=1,"A","B")
--
Milton Purdy
ACCESS
State of Arkansas


rciolkosz said:
I can make this expression work in my query but how do I add to the
expression to make the rest work.

IIf([# of bags]=1,"A")

How do I add if =2,"B"

Etc, Etc
Click to expand...
Click to expand...
 
F

fredg

I can make this expression work in my query but how do I add to the
expression to make the rest work.

IIf([# of bags]=1,"A")

How do I add if =2,"B"

Etc, Etc
Click to expand...

Are those the only possibilities, 1 or 2?
=IIf([# of bags]=1,"A","B")

If you have a couple of more possibilities, you can use:

NewColumn:IIf([# of bags]=1,"A",IIf([# of bags]=2,"B",IIf([# of
bags]=3,"C","D")))

Note that "D" will display if the [# of bags] is not 1 or 2 or 3.

If there are many more possibilities, then look up the Select Case
statement in VBA help.
 
J

Jerry Whittle

You could nest the IIf statement, but it gets ugly quick.

IIf([# of bags]=1,"A", IIf([# of bags]=2,"B","C"))

I usually won't nest more that 3 IIf's in the same statement. But what to do
then? Two choices.

1. Create a table like so:
Bags Rating
1 A
2 B
3 C and so on.

Then join this table in a query. This works good if there is a 1-1
relationship between the bags and ratings. However once you get into
something like Between 5 and 10 = D, it has problems.

2. You could create a public function in a module that uses Case. It's much
easier to maintain.

Function fBags(strBags As Variant) As String
Dim TheBags As String
Select Case strBags
Case = 1
TheBags = "A"
Case = 2
TheBags = "B"
Case 3 To 10
TheBags = "C"
Case Else ' Other values.
TheBags = "Z"
End Select
fBags = TheBags
End Function

In a query:

TheRate: fBags([# of bags])
 
J

John W. Vinson

I can make this expression work in my query but how do I add to the
expression to make the rest work.

IIf([# of bags]=1,"A")

How do I add if =2,"B"

Etc, Etc
Click to expand...

If you want to convert 1 to "A", 2 to "B", ..., 26 to "Z" then you could use
the Choose() function:

Choose([# of bags], "A", "B", "C", "D", "E", "F", <etc. through Z>, "Invalid")
 
Top