Help with Case Statement

[lurch279]

Hi folks!

I have two fields in a query txttext1 and txttext2 that I would like to
evaluate and based upon the result put a value in txttext3.

What I would like to do is call a function within my Access 2000 query
that would take a value of text1 and a value from text2 and give me a
'return' in text3

So if text1 = "Abnormal" and text2 = "red" then text3 = 'It is good"
but if text1 = "Normal" and text2 = "Green" then text3 = "This is not
good"

I cannot use iif as I will need about 15 nested iif's (plus it'd
confuse the hell outa me) - I have alot to compare

I can do this if I evaluate just one field but I don't have the 'know
how' to add a second evaluation.....I'm just a beginner!!

Any help you guys could provide would be MOST appreciated!!

Thanks!

Lurch

 

[Duane Hookom]

Can you create small lookup table with unique values of txtText1 and
txtText2 with the resulting text3 value. Then just join this lookup and
maintain data rather than expressions.

 

[David Cox]

completely off the top of my head, untested, and long after my bedtime:-

iif(10*instr("abnormalnormalcase3case4",text1)+instr("redgreenvioletpink",text2)
in(11,whatever,whatever), "It is good","BAD")

 

[David Cox]

I could not go to bed having advocated such bad practise.

much better documented would be something like:
iif( text1 & "_" & text2 in ("abnormal_red","whatever_something",....),"It
is good","It is bad")

 

[lurch279]

Thanks Guys but I'm not sure if I can do that....Duane - I like your
lookup idea but my tables are linked to an external database.

Here's what I was trying to do (keep in mind I know nothing so, if you
could, explain this like I was a 10 year old!! hehehe)

Public Function Region(Province_State As String, Area_Code As String)
As String
Select Case
Case Province_State = Ontario And Area_Code = 519
Region = "Ontario South"
Case Province_State = Ontario And Area_Code = 807
Region = "Ontario North"
....and so on.....
End Select

End Function

I'm sure you could understand why I cannot use IIF's as I have to do
every state, province and area code that we sell/ship to - and
combinations thereof.

I thought that if I coded a Case statement I could do it more easily.

 

[Duane Hookom]

A public function would have been my second choice. You created a great
start on the function. I think this should work:

Public Function Region(Province_State As String, _
Area_Code As String) As String
Select Case True
Case Province_State = "Ontario" And Area_Code = "519"
Region = "Ontario South"
Case Province_State = "Ontario" And Area_Code = "807"
Region = "Ontario North"
....and so on.....
Case Else
Region = "unknown"
End Select

End Function