"IIF" in Criterea area of Query

[JohnG]

I've created a Microsoft Access2000 form in which users can select several
parameters for including or excluding data on a report

All brokerage firms or an individual brokerage firm can be selected, all
regions or an individual region can be selected and all reps or an individual
rep can be selected. (Eg., the user would select an individual region from a
Combo Box or he would Check a Box (A Check Box) called "AllReps"

When the user selects any combination of these parameters, on the Form a
hidden Hold Text Box for Brokerage Firm, Region and Rep will contain either
Null (for selecting All values) or the actual value selected for Brokerage
Firm, Region or Rep . (These hold fields are scrutinized in the report
query)

The user will then press a command button to produce the report.

I want to have, ideally, just one query underlying this report but I'm having
difficulty with the criteria for selecting or excluding fields. On the
Criterea (Where condition) line of the Query Design view I've used a
conditional IIF for the fields like this one:

IIf([Forms]![Reports-Custom]![HoldRep]=Null,Not "ZZZ",[Forms]![Reports-
Custom]![HoldRep])

The idea of the "Not 'ZZZ' is that it would be a condition that would always
be True and hence all the Reps would be selected, but it's not working when
the HoldRep text box on the form is Null . It only works when there is a real
Rep code moved to that HoldRep text box on the form. It seems that an actual
value must present instead of what I have.
Any suggestions? Thanks, John

 

[HanSolo]

If I understand your problem, rather than using an IIF statement, how
about making your criteria:

Like [Forms]![Reports-Custom]![HoldRep] &"*"

This would result in all Reps being selected.

 

[JohnG]

IT WORKED!!!

Thanks, HanSolo!

And Thanks to this wonderful site where knowledge is freely shared!

JohnG

 

[George Nicholson]

[Forms]![Reports-Custom]![HoldRep]=Null
will always return False (or an error). Null never equates to anything, (not
even Null), so the statement will never be True.

IsNull([Forms]![Reports-Custom]![HoldRep])
should at least point your Iif() to the expected T/F response. (Whether "Not
'ZZZ'" executes as expected is a separate issue...)

HTH,