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

G

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.
 
G

Guest

Try this syntax in the query:

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

MGFoster

bicyclops said:
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-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top