How do I set up an IF/Else macro?

[Guest]

I am trying to create a macro to set a value that is determined by a
hierarchical IF statement. i.e. If condition 1 is not met, condition 2 is
checked, if condition 2 is not met, condition 3 is checked and so on. I am
using Access 2000

 

[Steve Schapel]

Ken,

On the basis of what you have told us so for, it is unlikely that this
is a job for a macro. Most likely you will be using a calculated field
in a query, or a calculated control on a form or report, using nested
IIf() functions, or more likely a Switch() function.

If you need more specific help, please post back with more details, with
examples.

 

[Guest]

I am using Access 2000. I am trying to create a macro to set a value of a
form field (AmtDue) based upon one of five nested conditions:

If CodeFieldA = "S" or CodeFieldA "M", then AmtDue = 0
ElseIf
If CodeFieldB Like "RB*, then AmtDue = 0
ElseIf
If RateFieldA > RateFieldB then AmtDue = RateFieldA - RateFieldB
ElseIf
If RateFieldA < RateFieldB then AmtDue = RateFieldA
ElseIf
If RateFieldA = RateFieldB then AmtDue = RateFieldB.
EndIf

Each subsequent test is based upon a False condition on the prior condition,
the last condition RateFieldA = RateFieldB is the default.

I've tried to code this as written in a calculated field within a form but
cannot figure how to code it as a nested IIf() function, with an else
parameter.

 

[Douglas J. Steele]

That's not what I posted as the answer for you yesterday!

It should be:

If CodeFieldA = "S" or CodeFieldA = "M" Then
AmtDue = 0
ElseIf CodeFieldB Like "RB* Then
AmtDue = 0
ElseIf RateFieldA > RateFieldB Then
AmtDue = RateFieldA - RateFieldB
ElseIf RateFieldA < RateFieldB Then
AmtDue = RateFieldA
Else
AmtDue = RateFieldB.
EndIf

The IIf function has 3 parts: a boolean expression, what to do if the
boolean expression is True and what to do it's it False. You need to put
another IIf statement as the False part.

Your final statement will be something like the following (watch the
word-wrap!)


IIf (CodeFieldA = "S" or CodeFieldA = "M", AmtDue = 0, _
IIf (CodeFieldB Like "RB*, AmtDue = 0, _
IIf (RateFieldA > RateFieldB, AmtDue = RateFieldA - RateFieldB, _
IIf (RateFieldA < RateFieldB, AmtDue = RateFieldA, AmtDue =
RateFieldB))))

 

[Guest]

Thanks. I've tried coding the nested IIF as you described. I'm assuming
that it isn't working because of a size limitation on the number of
characters that can be used. I had shortened the actual field names in my
example for clarity and ease of reading but the actual field names were
longer. I'll go back to my form and try re-defining the fields so the IIF
statement won't be truncated.

Once again, thanks for the help. It's rough being a newbie without someone
to go to.

 

[Steve Schapel]

Ken,

As mentioned in my earlier post, an alternative approach might be the
Switch() function. Using Doug's example, it would look like this...
Switch(CodeFieldA="S" Or CodeFieldA="M" Or CodeFieldB Like
"RB*,0,RateFieldA>RateFieldB,RateFieldA-RateFieldB,RateFieldA<RateFieldB,RateFieldA,True,RateFieldB)

 

[Guest]

I agree, that seems to be a cleaner approach. I'll try it. Thanks

 

[Guest]

Thanks to all that replied.

The switch function did the trick. I now see that my attempt at using the
IIF(condition,true,false) replacing the false parameter with the next IIF
statement would have worked if I had only used one closing parenthesis
instead of one for each IIF statement. The syntax rules for nested if
statements wasn't very clear. I was used to languages which required an
endif (in this case, the closing prenthesis) for each IF statement.

Once again, thanks for all your help.