Query Problem

[Wfuphan]

Hello,
I'm having a problem with two queries that I want to interact. I have one query that searches for a certain Document Status and the other that searches for Document Type. I want to be able to search for a particular type of document and then continue to narrow the search by entering the status of the document. However, when these two queries are connected with an AND statement, I can't leave one query blank because all I receive is an empty table.
So, I guess the question is, how do I set this up so that an empty field will return all records?

Thanks

 

[elinorigb]

I'm assuming that you're using some type of user input in your query? It may be a matter of using wildcards in your search. In the query design view, under your fields for Status and Type, you could include this type of statement:

Like [Enter the doc status:] & "*"

and a similar statement for the document Type.

That way if you don't enter anything for Type or Status (or both) you should get all of them, instead of none. Since I haven't seen your query, I'm not sure if this answers your question. You may not even need to separate queries. You may just need one query that searches those two fields.

 

[Wfuphan]

This solution is better, however it still doesn't return the results I was looking for. When I leave the Type query field blank I only get back the status of documents that have been assigned a type all other documents are left out. Yet, when I enter the Type and leave the Status field blank, it works as expected. Here's my query: SELECT [Outgoing Doc Log].[Log Number], [Outgoing Doc Log].[Document Number], [Outgoing Doc Log].[Document Title], [Outgoing Doc Log].Status, [Outgoing Doc Log].Author, [Outgoing Doc Log].[Document Type]
FROM [Outgoing Doc Log]
WHERE ((([Outgoing Doc Log].Status) Like [In-progress, Post Check, or Completed?] & "*") AND (([Outgoing Doc Log].[Document Type]) Like [Narrow search by entering Doc Type] & "*"));
Any help would be greatly appreciated.

Thanks

 

[Wfuphan]

Any takers?

 

[PHV]

Try something like this:
SELECT [Log Number], [Document Number], [Document Title], Status, Author, [Document Type]
FROM [Outgoing Doc Log]
WHERE Nz(Status, "") Like [In-progress, Post Check, or Completed?] & '*'
AND Nz([Document Type], "") Like [Narrow search by entering Doc Type] & '*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wfuphan]

That works marvelously!! Thanks a lot