need query to show nulls sometimes, exclude them other times.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bicyclops - 05 May 2005 18:55 GMT

On my form that shows a list of part numbers, I have combo boxes that filter
for the various fields. The Criteria lines in the underlying query point to
these combo boxes. Some of the fields have nulls. The Right now my Criteria
lines look like:

Like [Forms]![FrmACI-PN]![CboDescription] & "*" Or Is Null

But using this, all of the records with nulls show up when something is
selected in the box. How can I make the records with nulls show when the box
is blank, but be filtered out when there is data in the box?

Thanks in advance.

Reply to this Message

jl5000 - 05 May 2005 20:29 GMT

Try this syntax in the query:

Field: nz(MyFieldName,"FieldIsNull")
Table:MyTable
Criteria:Like nz([Forms]![FrmACI-PN]![CboDescription],"FieldIsNull") & "*"

Signature

jl5000
<a href="http://joshdev.com"></a>

> On my form that shows a list of part numbers, I have combo boxes that filter
> for the various fields. The Criteria lines in the underlying query point to
[quoted text clipped - 8 lines]
>
> Thanks in advance.

Reply to this Message

MGFoster - 05 May 2005 20:32 GMT

> On my form that shows a list of part numbers, I have combo boxes that filter
> for the various fields. The Criteria lines in the underlying query point to
[quoted text clipped - 6 lines]
> selected in the box. How can I make the records with nulls show when the box
> is blank, but be filtered out when there is data in the box?

Switch to SQL view. In the WHERE clause, LIKE expression put this:

((column_name LIKE Forms![FrmACI-PN]!cboDescription & "*"
AND Forms![FrmACI-PN]!cboDescription IS NOT NULL)
OR Forms![FrmACI-PN]!cboDescription IS NULL)

Substitute your column's (field) name for "column_name."

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message