Switch statement help

[igendreau]

I am writing a query that uses a switch statement. Here's my
statement:

Region: Switch([STATE_CODE]="PA" Or
[STATE_CODE]="DE","PennDel",[STATE_CODE]="NY" Or
[STATE_CODE]="NJ","Atlantic")

Basically I have a new field named "Region". If the STATE_CODE value
is PA or DE, then Region="PennDel". If the STATE_CODE value is NY or
NJ, then Region="Atlantic".

The query works like a charm right now, but I need to add a piece to my
statement. I need to add a piece that says "If the STATE_CODE value is
not one of: PA, DE, NY or NJ, then Region=Other". Or it could be
similar to an IF...Else statement. Where if the value of STATE_CODE
doesn't fit one of the other options, make it "Other".

Not sure how to write it correctly. Can someone help?

[need.accessresource]

Region: Switch([STATE_CODE]="PA" Or
[STATE_CODE]="DE","PennDel",[STATE_CODE]="NY" Or
[STATE_CODE]="NJ","Atlantic", "Steve needs this answer"=True,"Tell PC
Datasheet we miss him")

Chris Mills
Your Access Resource

http://www.accessresource.somee.com/Specials.html

[Lyle Fairfield]

Wouldn't everyone would use a simple class for this?

Dim aRegion(65 To 90, 65 To 90) As String

Private Sub Class_Initialize()
aRegion(Asc("D"), Asc("E")) = "PennDel"
aRegion(Asc("N"), Asc("J")) = "Atlantic"
aRegion(Asc("N"), Asc("Y")) = "Atlantic"
aRegion(Asc("P"), Asc("A")) = "PennDel"
End Sub

Property Get Region(ByVal StateCode As String) As String
Dim b() As Byte
StateCode = StrConv(Left(StrConv(StateCode, vbUpperCase), 2),
vbFromUnicode)
b = StateCode
Region = aRegion(b(0), b(1))
If Len(Region) = 0 Then Region = "Other"
End Property

and then call a UDF (that referenced the class) in the query.

[inkman04]

Hello.

Please excuse my ignorance but couldn't it be done by
just adding a bit more to your original code by adding a
comma and then "Other"? See below.

Region: Switch([STATE_CODE]="PA" Or
[STATE_CODE]="DE","PennDel",[STATE_CODE]="NY" Or
[STATE_CODE]="NJ","Atlantic","Other")

Or is it ...."Atlantic", True, "Other")

Perhaps the Switch function example can be followed?

Have a nice day.

Regards