Conditional Statements in a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to add an If/Then statement with more than one condition. I either
get a syntax error or it does not work correctly. My statement is
this....=IIf([CountOfAcct #]<"6","225",IIf([CountOfAcct
#]Between"6"and"10","300","400")). I have Count of Accts in all three
catagories but it does not seem to work. Any suggestions?
 
You can't use Between in IIf statements (nor in If statements, for that
matter). Try:

=IIf([CountOfAcct #] < "6","225",IIf([CountOfAcct >= "6" and [CountOfAcct]
<= "10","300","400"))
 
Trying to add an If/Then statement with more than one condition. I either
get a syntax error or it does not work correctly. My statement is
this....=IIf([CountOfAcct #]<"6","225",IIf([CountOfAcct
#]Between"6"and"10","300","400")). I have Count of Accts in all three
catagories but it does not seem to work. Any suggestions?
Click to expand...

Is CountOfAcct a Text field? If not, you should lose the quotemarks.
The text string "137" is less than the text string "2" - my guess is
that you would consider the Count 137 to be *larger* than the count 2!

For multiple conditions, you can use nested IIF's, but for more than
two conditions, the Switch() function may be simpler. It takes
arguments in pairs, and reads them left to right. When it encounters a
pair where the first member is True, it returns the second member of
the pair and quits. So:

=Switch([CountOfAcct #] < 6, "225", [CountOfAcct #] <= 10, "300",
True, "400")

The last pair has a literal True as the first argument, so 400 will be
returned if none of the other pairs pass muster.

John W. Vinson[MVP]
 
Back
Top