How do I set up an IF/Else macro?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken@AP&T - 26 Oct 2004 22:11 GMT

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

Reply to this Message

Steve Schapel - 27 Oct 2004 18:38 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> 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

Reply to this Message

Ken@AP&T - 28 Oct 2004 02:29 GMT

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.

Reply to this Message

Douglas J. Steele - 28 Oct 2004 03:00 GMT

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))))

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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:
[quoted text clipped - 16 lines]
> cannot figure how to code it as a nested IIf() function, with an else
> parameter.

Reply to this Message

Ken@AP&T - 28 Oct 2004 20:11 GMT

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.

> That's not what I posted as the answer for you yesterday!
>
[quoted text clipped - 46 lines]
> > cannot figure how to code it as a nested IIf() function, with an else
> > parameter.

Reply to this Message

Steve Schapel - 28 Oct 2004 20:25 GMT

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)

Signature

Steve Schapel, Microsoft Access MVP

> 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
[quoted text clipped - 5 lines]
> Once again, thanks for the help. It's rough being a newbie without someone
> to go to.

Reply to this Message

Ken@AP&T - 28 Oct 2004 21:38 GMT

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

> Ken,
>
[quoted text clipped - 12 lines]
> > Once again, thanks for the help. It's rough being a newbie without someone
> > to go to.

Reply to this Message

Ken@AP&T - 29 Oct 2004 00:02 GMT

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.

Reply to this Message