Parameter Query w/ search form

[Guest]

I have been working on another parameter query with a search box. I created
a parameter query with 7 search fields. I need the users to be able to enter
parameters in any combination. If they don’t enter anything for a certain
parameter, I need it to return records even if those fields are blank. I
created a form to enter the criteria (SearchBox). I have entered the
following into the query. But I it does not seem to work correctly. I am
having problems with mainly with the dates. If I don’t enter any dates, I
don’t get any records unless there is something in the all the date fields.
If any one of the fields it blank it does not show up.
If you need more info, please let me know.
Again thanks and sorry for all the questions in one day.

FIELDS Criteria
Region Like “*†& [Forms]![SearchBox]![Region] & “*â€
CompanyID Like “*†& [Forms]![SearchBox]![CompanyID] & “*â€
CallDate Like “*†& [Forms]![SearchBox]![CallDate] & “*â€
SchedDate Like “*†& [Forms]![SearchBox]![SchedDate] & “*â€
CompDate Like “*†& [Forms]![SearchBox]![CompDate] & “*â€
Tech Like “*†& [Forms]![SearchBox]![Tech] & “*â€
Manager Like “*†& [Forms]![SearchBox]![Manager] & “*â€

 

[Guest]

You need to test for OR IS NULL in the case of each parameter, doing this
within parentheses in each case so each Boolean OR operation evaluates
independently before the Boolean AND operations, e.g.

SELECT *
FROM MyTable
WHERE
(Region = [Forms]![SearchBox]![Region]
OR [Forms]![SearchBox]![Region] IS NULL)
AND
(CompanyID = [Forms]![SearchBox]![CompanyID]
OR [Forms]![SearchBox]![CompanyID] IS NULL)
AND
(CallDate = [Forms]![SearchBox]![CallDate]
OR [Forms]![SearchBox]![CallDate] IS NULL)
AND
< and so on to >
(Manager = [Forms]![SearchBox]![Manager]
OR [Forms]![SearchBox]![Manager] IS NULL);

Ken Sheridan
Stafford, England

 

[Guest]

Thank you very much. This works beautifully. I owe you big.
--
JAD

You need to test for OR IS NULL in the case of each parameter, doing this
within parentheses in each case so each Boolean OR operation evaluates
independently before the Boolean AND operations, e.g.

SELECT *
FROM MyTable
WHERE
(Region = [Forms]![SearchBox]![Region]
OR [Forms]![SearchBox]![Region] IS NULL)
AND
(CompanyID = [Forms]![SearchBox]![CompanyID]
OR [Forms]![SearchBox]![CompanyID] IS NULL)
AND
(CallDate = [Forms]![SearchBox]![CallDate]
OR [Forms]![SearchBox]![CallDate] IS NULL)
AND
< and so on to >
(Manager = [Forms]![SearchBox]![Manager]
OR [Forms]![SearchBox]![Manager] IS NULL);

Ken Sheridan
Stafford, England

I have been working on another parameter query with a search box. I created
a parameter query with 7 search fields. I need the users to be able to enter
parameters in any combination. If they don’t enter anything for a certain
parameter, I need it to return records even if those fields are blank. I
created a form to enter the criteria (SearchBox). I have entered the
following into the query. But I it does not seem to work correctly. I am
having problems with mainly with the dates. If I don’t enter any dates, I
don’t get any records unless there is something in the all the date fields.
If any one of the fields it blank it does not show up.
If you need more info, please let me know.
Again thanks and sorry for all the questions in one day.

FIELDS Criteria
Region Like “*†& [Forms]![SearchBox]![Region] & “*â€
CompanyID Like “*†& [Forms]![SearchBox]![CompanyID] & “*â€
CallDate Like “*†& [Forms]![SearchBox]![CallDate] & “*â€
SchedDate Like “*†& [Forms]![SearchBox]![SchedDate] & “*â€
CompDate Like “*†& [Forms]![SearchBox]![CompDate] & “*â€
Tech Like “*†& [Forms]![SearchBox]![Tech] & “*â€
Manager Like “*†& [Forms]![SearchBox]![Manager] & “*â€

 

[Guest]

Hi Ken,
I had this working til late yesterday. After running the query a few times
I started getting an error "Query is too complex" .
Would you happen to know why this would work a few times, then get this error?
Thanks
--
JAD

You need to test for OR IS NULL in the case of each parameter, doing this
within parentheses in each case so each Boolean OR operation evaluates
independently before the Boolean AND operations, e.g.

SELECT *
FROM MyTable
WHERE
(Region = [Forms]![SearchBox]![Region]
OR [Forms]![SearchBox]![Region] IS NULL)
AND
(CompanyID = [Forms]![SearchBox]![CompanyID]
OR [Forms]![SearchBox]![CompanyID] IS NULL)
AND
(CallDate = [Forms]![SearchBox]![CallDate]
OR [Forms]![SearchBox]![CallDate] IS NULL)
AND
< and so on to >
(Manager = [Forms]![SearchBox]![Manager]
OR [Forms]![SearchBox]![Manager] IS NULL);

Ken Sheridan
Stafford, England

I have been working on another parameter query with a search box. I created
a parameter query with 7 search fields. I need the users to be able to enter
parameters in any combination. If they don’t enter anything for a certain
parameter, I need it to return records even if those fields are blank. I
created a form to enter the criteria (SearchBox). I have entered the
following into the query. But I it does not seem to work correctly. I am
having problems with mainly with the dates. If I don’t enter any dates, I
don’t get any records unless there is something in the all the date fields.
If any one of the fields it blank it does not show up.
If you need more info, please let me know.
Again thanks and sorry for all the questions in one day.

FIELDS Criteria
Region Like “*†& [Forms]![SearchBox]![Region] & “*â€
CompanyID Like “*†& [Forms]![SearchBox]![CompanyID] & “*â€
CallDate Like “*†& [Forms]![SearchBox]![CallDate] & “*â€
SchedDate Like “*†& [Forms]![SearchBox]![SchedDate] & “*â€
CompDate Like “*†& [Forms]![SearchBox]![CompDate] & “*â€
Tech Like “*†& [Forms]![SearchBox]![Tech] & “*â€
Manager Like “*†& [Forms]![SearchBox]![Manager] & “*â€