Query Help (2000)

I'm trying to write a "search" query that will allow me to search by date, title, and department (or any combination thereof). I have created a form that will allow a user to search by these different parameters.

Here's my SQL:
SELECT qryOrdersAll.*
FROM qryOrdersAll
WHERE (((qryOrdersAll.DEPT)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.ORDER_DATE) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate])) OR (((qryOrdersAll.ORDER_DATE) Between [Forms]!
[frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.TITLE) Like "*" & [Forms]!
[frmTechServicesOrdering]![txtTitleSearch] & "*"));

It's searching by date and title just fine, but when I try
to search by date and department, it finds all of the
records. I am passing the department ID number, which is
the DEPT field. The DEPT field is a text field.

Re: Query Help (2000)

The reason all records are coming back is because of the OR statement. When you select Date and Department, the Or statement, in effect, says return All Records between the two dates because of the following:
((qryOrdersAll.TITLE) Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*"));. The *s cause all records to return when the text box is null. Try removing the *s to see if it fixes the date and department search (it should). If you need to keep the *s, add a field in your query that references [Forms]![frmTechServicesOrdering]![txtTitleSearch] and set the criteria to Null in the first part of your statement. This will allow you greater control over how the query should run.
HJope that helps.

Re: Query Help (2000)

Dashiell has already explained that the OR is the problem. You can also try to use AND instead of OR. In that case, the WHERE part can be simplified to

WHERE (ORDER_DATE Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND DEPT=[Forms]![frmTechServicesOrdering]![txtDeptTest] AND TITLE Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*";

Re: Query Help (2000)

When I do what you have suggested Hans, it will now look up the Departments but not search by the title/date range combination. Any thoughts?

Dashiell, can you give me a little bit more info on what you mean by:

"Try removing the *s to see if it fixes the date and department search (it should). If you need to keep the *s, add a field in your query that references [Forms]![frmTechServicesOrdering]![txtTitleSearch] and set the criteria to Null in the first part of your statement. This will allow you greater control over how the query should run."

Re: Query Help (2000)

Try the attached statement (it provides a long answer but should help you see what I mean by creating a field based on your form and then using Null to help contol the query):

SELECT qryOrdersAll.*
FROM qryOrdersAll
WHERE (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch])) OR (((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null)) OR (((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR (((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR ((([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null));

Re: Query Help (2000)

I'm using part of a string (sorry that I didn't make that more clear initially)...

Actually, I think I got it (with a little help from the MS Newsgroups):

SELECT qryOrdersAll.*
FROM qryOrdersAll
WHERE ((([qryOrdersAll].[DEPT])=[Forms]![frmTechServicesOrdering]![txtDeptTest]) And (([qryOrdersAll].[ORDER_DATE]) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate])) Or ((([qryOrdersAll].[ORDER_DATE]) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) And ([Forms]![frmTechServicesOrdering]![txtTitleSearch] Is Not Null) And (([qryOrdersAll].[TITLE]) Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*"));