IIf Statement using "In"

[Guest]

I have a query where I'm trying to rename a field if it meets certain
criteria. Example:

IIf([Group]In("A", "B", "C"), "Atlanta", IIF([Group]In("D", "E", "F"),
"East", IIf([Group]In ("G", "H", "I"), "North", "West")))

Am I writing the "In" part of the statement incorrectly?

 

[Duane Hookom]

I would create a small lookup table like:

tblGroupTerritory
====================
Group Territory
A Atlanta
B Atlanta
C Atlanta
D East
E East
F East
G North
H North
I North
.... West
.... West
.... West
.... West

This will allow you to manage business specs in data and joins rather than
in expressions. If you can't or won't create the lookup table, I would write
a small function so that this logic is maintained in only one place, not
expressions in query or other places.

Public Function GetTerritory(pstrGroup as String) as String
Select Case pstrGroup
Case "A","B","C"
GetTerritory = "Atlanta"
Case "D","E","F"
GetTerritory ="East"
.... etc...
Case Else
GetTerritory = "West"
End Select
End Function

You could then use this funtion where ever you want to change a Group to a
Territory.

 

[Guest]

Where do I put that logic? That looks like VB code. Please advise. Thanks.

 

[Duane Hookom]

I provided two solutions. One was a query/sql solution that should require
no code.

The second solution requires you create a new blank module and paste the
code from "Public Function..." to "End Function" into the module window. You
would then save the module as "basBusinessCalcs". The function could be used
in a query like:

Territory: GetTerritory([Group])

--
Duane Hookom
MS Access MVP

Where do I put that logic? That looks like VB code. Please advise.
Thanks.

 

[Guest]

I had never used a Module before but after seeing the results from your
suggestion, I'm sure to use them more often now. Thanks, it worked like a
charm.

 

[Duane Hookom]

pretty cool eh?

--
Duane Hookom
MS Access MVP

I had never used a Module before but after seeing the results from your
suggestion, I'm sure to use them more often now. Thanks, it worked like a
charm.
--
If you never attempt anything, you will never make any mistakes...thus
never
enjoying the fruits of accomplishment.

I provided two solutions. One was a query/sql solution that should
require
no code.

The second solution requires you create a new blank module and paste the
code from "Public Function..." to "End Function" into the module window.
You
would then save the module as "basBusinessCalcs". The function could be
used
in a query like:

Territory: GetTerritory([Group])