Including Null values in query design

[Gordon]

I am using a form to enter the parameters for a report. The parameters
are drawn from a number of combo boxes on the form. The report is
based on a query. Several of the query fields have expressions such
as:

Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]!
[frmReports]![cboIssuedBy])).

This works fine as long as the fields that are being queried have no
null values - if they do, then in the situation where I want the "*"
to apply, the query only returns the records which have a value.
Really, I want to be able to replace the "*" part of the expression
above with something along the lines of LIke "*" or Is Null but I
can't manage to make that work in the expression above.
Can anyone help?

Gordon

 

[Dennis]

AND and OR statements are allowed in IIFs. What syntax are you attempting to
use that is barfing for you?

 

[Dennis]

I would think that perhaps using the Nz() function might work for him.

I am using a form to enter the parameters for a report. The parameters
are drawn from a number of combo boxes on the form. The report is
based on a query. Several of the query fields have expressions such
as:

Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]!
[frmReports]![cboIssuedBy])).

This works fine as long as the fields that are being queried have no
null values - if they do, then in the situation where I want the "*"
to apply, the query only returns the records which have a value.
Really, I want to be able to replace the "*" part of the expression
above with something along the lines of LIke "*" or Is Null but I
can't manage to make that work in the expression above.

Like IIf(IsNull(Forms!frmReports!cboIssuedBy), "*",
Forms!frmReports!cboIssuedBy) OR Is Null

But his kind of glop quickly spins out of control. You
would be much better off if you'd use some code in your open
the report command button's click event procedure to
construct the OpenReport method's WhereCondition argument.
See http://allenbrowne.com/ser-62.html for an example of how
this can be done.

 

[Gordon]

I would think that perhaps using the Nz() function might work for him.

Gordon wrote:

I am using a form to enter the parameters for a report. The parameters
are drawn from a number of combo boxes on the form.  The report is
based on a query.  Several of the query fields have expressions such
as:
Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]!
[frmReports]![cboIssuedBy])).
This works fine as long as the fields that are being queried have no
null values - if they do, then in the situation where I want the "*"
to apply, the query only returns the records which have a value.
Really, I want to be able to replace the "*" part of the expression
above with something along the lines of LIke "*" or Is Null but I
can't manage to make that work in the expression above.

Like IIf(IsNull(Forms!frmReports!cboIssuedBy), "*",
Forms!frmReports!cboIssuedBy) OR Is Null

But his kind of glop quickly spins out of control.  You
would be much better off if you'd use some code in your open
the report command button's click event procedure to
construct the OpenReport method's WhereCondition argument.
Seehttp://allenbrowne.com/ser-62.htmlfor an example of how
this can be done.

- Show quoted text -

Thanks for the replies, guys. Marshall's solution nailed it in one.
I agree about the format - normally I would have used code but this
was something I inherited and was looking for a quick solution.
Thanks again.
Gordon

 

[Dennis]

Indeed.

I don't see how without using a calculated field and it
already complicated enough..

This whole approach is at best clumsy, and can possibly
cause a drastic performance drain. At worst, when there are
more than a very few fields with this kind of messy
criteria, the entire query becomes too complex to run.
--
Marsh
MVP [MS Access]

I would think that perhaps using the Nz() function might work for him.

Gordon wrote:
I am using a form to enter the parameters for a report. The parameters
are drawn from a number of combo boxes on the form. The report is
based on a query. Several of the query fields have expressions such
as:

Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]!
[frmReports]![cboIssuedBy])).

This works fine as long as the fields that are being queried have no
null values - if they do, then in the situation where I want the "*"
to apply, the query only returns the records which have a value.
Really, I want to be able to replace the "*" part of the expression
above with something along the lines of LIke "*" or Is Null but I
can't manage to make that work in the expression above.


Like IIf(IsNull(Forms!frmReports!cboIssuedBy), "*",
Forms!frmReports!cboIssuedBy) OR Is Null

But his kind of glop quickly spins out of control. You
would be much better off if you'd use some code in your open
the report command button's click event procedure to
construct the OpenReport method's WhereCondition argument.
See http://allenbrowne.com/ser-62.html for an example of how
this can be done.

.