By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

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

P: n/a
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"*"));
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?
Nov 12 '05 #2

P: n/a
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"*"));

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.