Query of records with various criteria incl. Null Values

[Guest]

My Access 2002 database lists everyone in an organization that has different
types of transportation passes. Wanting to keep a record of all employees
who's ever had a pass (even if they don't have one anymore), I included the
column "MonthCanceled". I'm trying to generate a list of employees who are
in the same department who are supposed to be receiving any type of pass.
So, when I run a query, I want to list all records of employees who...
1) are in the Same Department (Marketing - Product A, Marketing - Product
B, etc.),
2) has "N/A" OR a blank in the field "Pass #"
3) has a "No" in "XYZ" field AND
4) Has no data in the "MonthCanceled" field

So, what I did (in Design View, using the same table) is the following:
1) Field: LastName
Sort: Ascending

2) Field: FirstName

3) Field: Departmet
Like "Marketing*"

4) Field: PassType

5) Field: Pass #
Criteria: "N/A"
or: Is Null

6) Field: XYZ
Criteria: No

7) Field: MonthCanceled
Criteria: Is Null

All criteria entered are listed in the same row. But the employees that
have data in the MonthCanceled column are still showing up. How do I make
them not show up in the query result?

 

[Allen Browne]

You are using 2 rows under the [Pass #] field? If so, try Criteria of:
"N/A" Or Is Null
Otherwise Access will pick all fields that match the first row of the
criteria, and then include any fields where Pass # is null.


If that does not solve the problem, what data type is the MonthCanceled
field? (e.g. Text? Number?)

 

[Guest]

It looks like it worked! Thanks SO much! I feel like it was a really dumb
question now. I don't know why I couldn't figure it out myself. This was my
first time using this Discussion Group and I'm very impressed. Thanks again!

You are using 2 rows under the [Pass #] field? If so, try Criteria of:
"N/A" Or Is Null
Otherwise Access will pick all fields that match the first row of the
criteria, and then include any fields where Pass # is null.


If that does not solve the problem, what data type is the MonthCanceled
field? (e.g. Text? Number?)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My Access 2002 database lists everyone in an organization that has
different
types of transportation passes. Wanting to keep a record of all employees
who's ever had a pass (even if they don't have one anymore), I included
the
column "MonthCanceled". I'm trying to generate a list of employees who
are
in the same department who are supposed to be receiving any type of pass.
So, when I run a query, I want to list all records of employees who...
1) are in the Same Department (Marketing - Product A, Marketing - Product
B, etc.),
2) has "N/A" OR a blank in the field "Pass #"
3) has a "No" in "XYZ" field AND
4) Has no data in the "MonthCanceled" field

So, what I did (in Design View, using the same table) is the following:
1) Field: LastName
Sort: Ascending

2) Field: FirstName

3) Field: Departmet
Like "Marketing*"

4) Field: PassType

5) Field: Pass #
Criteria: "N/A"
or: Is Null

6) Field: XYZ
Criteria: No

7) Field: MonthCanceled
Criteria: Is Null

All criteria entered are listed in the same row. But the employees that
have data in the MonthCanceled column are still showing up. How do I make
them not show up in the query result?

 

[Allen Browne]

Hey, you're very welcome. If you are express a question clearly and
succinctly, you will usually get a good response.