Novice Question - Null in Query

  • Thread starter Thread starter Jenna
  • Start date Start date
J

Jenna

I'm trying to do what I think ought to be a very simple query. I have
one table and I'm using criteria to filter my results in the query.
I first had this, one in each of two fields:
Like "*" & [Enter project no: ] & "*"
Like "*" & [Enter archive type: ] & "*"

That would easily sort by those two fields. However, if the entry in
the Project no field was Null, I wouldn't get any of those in my list. So I
changed to this:
Like "*" & [Enter project no: ] & "*" Or Is Null
Like "*" & [Enter archive type: ] & "*"

When I left the Project no field empty (Null), it works great and
returns all of the archive type selected, regardless of whether project no
was null or not.
However, when I enter something in the Project no and in the archive
type fields, it returns all those records with a null project type and those
that meet the "Like" criteria, when I just want those that meet the "Like"
criteria.

Basically, if Project no is left blank, I want the query to filter by
Like "*" & [Enter project no: ] & "*" Or Is Null

If the Project no is filled in, I want it to filter only by
Like "*" & [Enter project no: ] & "*"

I thought maybe I could use Iif for this, but I can't get it to work
and I feel like I've tried every combination!
I would greatly appreciate any help...and please speak slowly, I'm new
at this :)
 
However, when I enter something in the Project no and in the archive
type fields, it returns all those records with a null project type and those
that meet the "Like" criteria, when I just want those that meet the "Like"
criteria.

Basically, if Project no is left blank, I want the query to filter by
Like "*" & [Enter project no: ] & "*" Or Is Null

If the Project no is filled in, I want it to filter only by
Like "*" & [Enter project no: ] & "*"
Click to expand...

Try a criterion of

WHERE ([ProjectNo] LIKE "*" & [Enter project no: ] & "*") OR
([ProjectNo] IS NULL AND [Enter project no: ] IS NULL)
 
Thanks so much for responding! I think what you provided is the right
idea...I got an even simpler version on another forum that I thought I'd
share back here:

Like "*" & [Enter project no: ] & "*" Or [Enter project no: ] Is Null

Thanks very much for helping out!

John Vinson said:
However, when I enter something in the Project no and in the archive
type fields, it returns all those records with a null project type and those
that meet the "Like" criteria, when I just want those that meet the "Like"
criteria.

Basically, if Project no is left blank, I want the query to filter by
Like "*" & [Enter project no: ] & "*" Or Is Null

If the Project no is filled in, I want it to filter only by
Like "*" & [Enter project no: ] & "*"
Click to expand...

Try a criterion of

WHERE ([ProjectNo] LIKE "*" & [Enter project no: ] & "*") OR
([ProjectNo] IS NULL AND [Enter project no: ] IS NULL)
Click to expand...
 
Back
Top