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

[Guest]

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.

 

[Guest]

Try this syntax in the query:

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

 

[MGFoster]

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnp0s4echKqOuFEgEQIErQCgtSxxMxR1i/voO6WQzdu1tW3uhbIAoI38
qW2vbc5rnxcVnP2XPop+6Z/P
=G7D1
-----END PGP SIGNATURE-----