IIf Statement using "In"

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Ess said:
Where do I put that logic? That looks like VB code. Please advise.
Thanks.
Click to expand...
 
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.
 
pretty cool eh?

--
Duane Hookom
MS Access MVP

Ess said:
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.


Duane Hookom said:
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])
Click to expand...
Click to expand...
 
Back
Top