use between statement in an iif statement

[Guest]

I need help with an iif statement in a query. Basically I have a form that
the person uses to either choose a month or choose all months using option
buttons. The statement reads -- IIf([Forms]![Invtrany
info]![Month]=99,Between 1 And 12,[Forms]![Invtrany info]![Month]). It works
fine if they choose a month, but not when they choose all months (I used 99
for the month number). However, the between statement works fine if it’s not
in the iif statement.

 

[Guest]

Try and use this instead
Like IIf([Forms]![Invtrany info]![Month]=99,"*",[Forms]![Invtrany
info]![Month])

 

[John Spencer (MVP)]

If that is supposed to be criteria, you cannot do it that way. You can pass
parameters (values), but NOT operators (between, =, >, etc)

One option would be to use criteria like the following
Between IIF([Forms]![Invtrany info]![Month]=99,1,[Forms]![Invtrany
info]![Month])
And IIF([Forms]![Invtrany info]![Month]=99,12,[Forms]![Invtrany info]![Month])

OR another option if your criteria are not too complex is to use something like this.

(YourField = [Forms]![Invtrany info]![Month] OR [Forms]![Invtrany info]![Month]
= 99)