Passing Parameters in Query Using "Or"

[Kindra]

Hi,

I'm new here and I'm trying to write a "search" query (I
work in a library) 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.

Can anyone help?

Thanks,
Kindra

 

[Ken Snell]

If I'm following your WHERE clause correctly (lots of ()s!), your last
"group" will return all records if the date value you give is within the
region even if you have no value for TITLE:

(((qryOrdersAll.ORDER_DATE) Between [Forms]!
[frmTechServicesOrdering]![txtStartDate] And [Forms]!
[frmTechServicesOrdering]![txtEndDate]) AND
((qryOrdersAll.TITLE) Like "*" & [Forms]!
[frmTechServicesOrdering]![txtTitleSearch] & "*"))

In the above, if [Forms]![frmTechServicesOrdering]![txtTitleSearch] is
"empty" or Null, then the code will return all TITLES because of the wild
card search that you're doing on that field. Therefore, it doesn't matter
which value you enter for DEPT, so long as the date is within the above
range, all records will be returned unless you enter some value for TITLE
too.

 

[Steve Schapel]

Kindra,

I think the problem is that your expression...
Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*"
.... will return all records if txtTitleSearch is blank.

I am not really sure what your desired outcome is... You said "any
combination thereof", which is not what you've got anyway. But maybe
one of the following WHERE clauses will help:

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] & "*"))

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 "*" & Nz([Forms]!
[frmTechServicesOrdering]![txtTitleSearch],"xfqzw") & "*"))
(... assumes that the Title field is unlikely to contain "xfqzw"!)

- Steve Schapel, Microsoft Access MVP

 

[Kindra]

The first query worked perfectly. Thanks so much!

Kindra

-----Original Message-----
Kindra,

I think the problem is that your expression...
Like "*" & [Forms]![frmTechServicesOrdering]! [txtTitleSearch] & "*"
.... will return all records if txtTitleSearch is blank.

I am not really sure what your desired outcome is... You said "any
combination thereof", which is not what you've got anyway. But maybe
one of the following WHERE clauses will help:

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] & "*"))

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 "*" & Nz([Forms]!
[frmTechServicesOrdering]![txtTitleSearch],"xfqzw") & "*"))
(... assumes that the Title field is unlikely to contain "xfqzw"!)

- Steve Schapel, Microsoft Access MVP

Hi,

I'm new here and I'm trying to write a "search" query (I
work in a library) 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.

Can anyone help?

Thanks,
Kindra

.