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].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*"));

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

This below does not:
SELECT [Glossary].GlossaryTermID , [Glossary].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE (([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSel ection!Status," *")) And
(([Glossary].MaintenanceCat egory) Like
Nz(Forms!JenSel ection!Maintena nceCategory"*") );

[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].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*"));

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

This below does not:
SELECT [Glossary].GlossaryTermID , [Glossary].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE (([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSel ection!Status," *")) And
(([Glossary].MaintenanceCat egory) Like
Nz(Forms!JenSel ection!Maintena nceCategory"*") );

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.co m> wrote in message
news:e3******** *************** ***@posting.goo gle.com...

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].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE ((([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*"));

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

This below does not:
SELECT [Glossary].GlossaryTermID , [Glossary].CoordinatorNam e,
[Glossary].Status, [Glossary].MaintenanceCat egory
FROM [Glossary]
WHERE (([Glossary].CoordinatorNam e) Like
Nz(Forms!JenSel ection!Coordina tor,"*")) And (([Glossary].Status) Like
Nz(Forms!JenSel ection!Status," *")) And
(([Glossary].MaintenanceCat egory) Like
Nz(Forms!JenSel ection!Maintena nceCategory"*") );