I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because
I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a
little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able
to calculate the value in 1 field instead of 4 fields.
I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small
sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):
Public Function IfThen(BYStep As String)
Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer
BYStep = "07.0" Then
test2 = Step070
ElseIf BYStep = "07.5" Then
test2 = Step075
ElseIf BYStep = "08.0" Then
test2 = Step080
Else: test2 = 0
In my query I have:
IfThen([BYStep]) AS Test2
It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I
don't get an error message, but the field Test2, comes up empty for every record.
Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field
in the query)?