Using Like Nz() in Access losing records when used more then once in query

[Jennifer]

I have a query with 5 possible criteria via a form. If criteria is
not entered, I use the like Nz() function on the backend query to use
an "*" for criteria fields left blank. The query is not returning the
full recordset. If the Like Nz() function is used on 1 field, it
returns the correct number of records. As soon as I add it on more
then one field, it loses records somehow. ???

For example,
SELECT [Glossary].GlossaryID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*"));

This returns the correct number of fields using the function on one
field.

This below does not:
SELECT [Glossary].GlossaryTermID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE (([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSelection!Status,"*")) And
(([Glossary].MaintenanceCategory) Like
Nz(Forms!JenSelection!MaintenanceCategory"*"));

[Salad]

Jennifer wrote:

I have a query with 5 possible criteria via a form. If criteria is
not entered, I use the like Nz() function on the backend query to use
an "*" for criteria fields left blank. The query is not returning the
full recordset. If the Like Nz() function is used on 1 field, it
returns the correct number of records. As soon as I add it on more
then one field, it loses records somehow. ???

For example,
SELECT [Glossary].GlossaryID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*"));

This returns the correct number of fields using the function on one
field.

This below does not:
SELECT [Glossary].GlossaryTermID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE (([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSelection!Status,"*")) And
(([Glossary].MaintenanceCategory) Like
Nz(Forms!JenSelection!MaintenanceCategory"*"));

So you are looking for all records where the coordinator name is null
and a status that is null and a maintenace category that is null. I
would expect your query is returning all records where those conditions
are true. Isn't that what you asked for?

[AR Hall]

Try the setting up your query using OR rather than AND
"Jennifer" <je***************@fmglobal.com> wrote in message
news:e3**************************@posting.google.c om...

I have a query with 5 possible criteria via a form. If criteria is
not entered, I use the like Nz() function on the backend query to use
an "*" for criteria fields left blank. The query is not returning the
full recordset. If the Like Nz() function is used on 1 field, it
returns the correct number of records. As soon as I add it on more
then one field, it loses records somehow. ???

For example,
SELECT [Glossary].GlossaryID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*"));

This returns the correct number of fields using the function on one
field.

This below does not:
SELECT [Glossary].GlossaryTermID, [Glossary].CoordinatorName,
[Glossary].Status, [Glossary].MaintenanceCategory
FROM [Glossary]
WHERE (([Glossary].CoordinatorName) Like
Nz(Forms!JenSelection!Coordinator,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSelection!Status,"*")) And
(([Glossary].MaintenanceCategory) Like
Nz(Forms!JenSelection!MaintenanceCategory"*"));