IIF Statement In Query Doesn't Work Right

[Daffy]

I have a query which contains an IIF statement that checks for a value in a
combo box in a form. It checks to see if it is null. If it is, then the
query must display ALL its values, and if it isn't, it must display only
those values from the combo box.

I have no problem displaying the values that match the values in the combo
box. My problem is as follows, when I tell it to display ALL its values, it
only choses to display rows that ARE NOT NULL, those omiting the NULL rows.

Here is my IIF statement:

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,[Length
(m)],[Forms]![frmSearch]![ComboCSLength])

Now, if I was to get rid of the IIF statement all together and replace it
with:

[Length (m)] Or Is Null

Is Null

my query displays all the values or the Null values respectively.

HOWEVER if I place any of tje above in my IIF statement like so:

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,([Length (m)] Or Is
Null),[Forms]![frmSearch]![ComboCSLength])

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,Is
Null,[Forms]![frmSearch]![ComboCSLength])

my query displays NO VALUES.

I've tried so many differen't things but I just can't get it to work.

What does work, is if I put Is Null in one of the Or fields, however, this
creates the problem of dipslaying Null values along with values from my combo
box when the IIF statement is FALSE.

PLEASE HELP ME I'M OUT OF IDEAS and DO NOT WANT TO HAVE NONE NULL VALUES.

Daffy

 

[Allen Browne]

Switch the query to SQL View.
Locate the WHERE clause.

Change it like this:
WHERE IIf([Forms]![frmSearch]![ComboCSLength] Is Null, True,
[Length (m)] = [Forms]![frmSearch]![ComboCSLength])

Explanation: A WHERE clause ultimately is an expression that returns a True
or False (or perhaps a Null.) For each record where it evaulates to TRUE,
the record is included; otherwise it is rejected. The expression above
returns something that is TRUE when the combo is null, of if the combo is
not null, it compares the field to the combo.

 

[John Spencer]

Try entering the following as the criteria for the field. Access will
rearrange this when you save the query, but it will work unless you try to do
this with several sets of criteria at once.

Field: YourFieldName
Criteria: =[Forms]![frmSearch]![ComboCSLength] OR
[Forms]![frmSearch]![ComboCSLength] Is Null

In an SQL where clause that would read:

WHERE (SomeField =[Forms]![frmSearch]![ComboCSLength] OR
[Forms]![frmSearch]![ComboCSLength] Is Null)

Another option (will not use any indexes and may be slow)
WHERE IIF([Forms]![frmSearch]![ComboCSLength] Is Null, True,
[FieldName]=[Forms]![frmSearch]![ComboCSLength])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Daffy]

Thanks for the help, the Query works perfectly.

Try entering the following as the criteria for the field. Access will
rearrange this when you save the query, but it will work unless you try to do
this with several sets of criteria at once.

Field: YourFieldName
Criteria: =[Forms]![frmSearch]![ComboCSLength] OR
[Forms]![frmSearch]![ComboCSLength] Is Null

In an SQL where clause that would read:

WHERE (SomeField =[Forms]![frmSearch]![ComboCSLength] OR
[Forms]![frmSearch]![ComboCSLength] Is Null)

Another option (will not use any indexes and may be slow)
WHERE IIF([Forms]![frmSearch]![ComboCSLength] Is Null, True,
[FieldName]=[Forms]![frmSearch]![ComboCSLength])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a query which contains an IIF statement that checks for a value in a
combo box in a form. It checks to see if it is null. If it is, then the
query must display ALL its values, and if it isn't, it must display only
those values from the combo box.

I have no problem displaying the values that match the values in the combo
box. My problem is as follows, when I tell it to display ALL its values, it
only choses to display rows that ARE NOT NULL, those omiting the NULL rows.

Here is my IIF statement:

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,[Length
(m)],[Forms]![frmSearch]![ComboCSLength])

Now, if I was to get rid of the IIF statement all together and replace it
with:

[Length (m)] Or Is Null

Is Null

my query displays all the values or the Null values respectively.

HOWEVER if I place any of tje above in my IIF statement like so:

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,([Length (m)] Or Is
Null),[Forms]![frmSearch]![ComboCSLength])

IIf([Forms]![frmSearch]![ComboCSLength] Is Null,Is
Null,[Forms]![frmSearch]![ComboCSLength])

my query displays NO VALUES.

I've tried so many differen't things but I just can't get it to work.

What does work, is if I put Is Null in one of the Or fields, however, this
creates the problem of dipslaying Null values along with values from my combo
box when the IIF statement is FALSE.

PLEASE HELP ME I'M OUT OF IDEAS and DO NOT WANT TO HAVE NONE NULL VALUES.

Daffy