Help Needed

[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?
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

 

[PHV]

Are Status and Document Type defined both as Text fields in the Outgoing Doc Log table ?

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

 

[Wfuphan]

yes

 

[PHV]

The table is an access one or a sql server one ?
Your query is in a mdb or adp database ?

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

 

[Wfuphan]

The table was made in access, and the query is in a mdb database.

 

[PHV]

So, this should work:
SELECT [Log Number], [Document Number], [Document Title], Status, Author, [Document Type]
FROM [Outgoing Doc Log]
WHERE Status Like [In-progress, Post Check, or Completed?] & '*'
AND [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, but is there a way where I can leave the fields blank and it will return all values, instead of just the records that have a specified document type?

 

[PHV]

What happens when you enter nothing in the [Narrow search by entering Doc Type] InputBox ?

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

 

[Wfuphan]

Well, if I specify a status to look for, it returns all the documents that have been given a document type for that particular status. For example, if I specify "Completed" it will return all the Completed documents that I have assigned a type to. However, if the type field is empty it skips over the document.

 

[PHV]

Have you tried this ?
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