Selecting Null through an IIF

  • Thread starter Thread starter Phill
  • Start date Start date
P

Phill

i am trying to include a blank option in a query based on
a combo box on a form

The form name is "frmSearchMethod" and the control
is "SCH1", what i would like the query criteria to based
on is if there is an item select from the combo box then
that is the criteria, if it is left blank, then the
criteria is "is null" to display all the blank items.
However using the following IIF's (below), it doesn't
bring back any records, but when using "is null" as the
criteria by itself i get 57. Does anyone have any
suggestions pleeease as to why i'm getting nothing back

Thank you

Phill

IIf([Forms]![frmSearchMethod]![SCH1].[value] Is Null,is
null,[Forms]![frmSearchMethod]![SCH1].[value])

IIf([Forms]![frmSearchMethod]![SCH1] = "",Null,[Forms]!
[frmSearchMethod]![SCH1].[value])

and the other 2 variations of this. None work!!! :(
 
Try:

IIf(IsNull([Forms]![frmSearchMethod]![SCH1].[value]),Null,[Forms]![frmSearch
Method]![SCH1].[value])
 
Phill,

[Forms]![frmSearchMethod]![SCH1] Or (Is Null And
[Forms]![frmSearchMethod]![SCH1] Is Null)

Note that this is all on one line... watch for wordwrap in the
newsreader. Also note that if you type it like this in the query
criteria, after you save the query Access will most likely re-arrange
the layout of it to suit its own way of thinking... don't worry about
this!

- Steve Schapel, Microsoft Access MVP
 
Back
Top