Custom Search Form Query

[randallst]

Hi There,

Im a newby to Access (and the forum too!).

I have made a database for work and is fully functional, but theres one thing I want to add but cant get my head around how to do it.

I have created a Form called 'Filtered Search', on the form it has multiple combo boxes for 'Auditors' 'Area' 'Status' and 2 text boxes for date range.

I want to be able to set what filters I want, and for the query to ignore any fields with no information selected/inputted (i.e. I want to see all records raised by "Mr Smith" (Auditor) that are still 'Active' (Status) in all areas at any time).

Hopefully that makes sense, if not, i'll try and reword it aha!

All the best
Stu

--------

Names of items which might help;

Table = 'Incidents'
Form = 'Filtered Search'
Report = 'Filtered Report'

Auditor = 'Combo7'
Status = 'Combo156'
Area = 'Combo5'
Date Range From = 'Text161'
Date Range To = 'Text163'

 

[CJ_London]

in your query criteria instead of saying (for example)


Status = forms![filtered search]!Combo156

you would use

Status = forms![filtered search]!Combo156 is null OR forms![filtered search]!Combo156

 

[randallst]

in your query criteria instead of saying (for example)


Status = forms![filtered search]!Combo156

you would use

Status = forms![filtered search]!Combo156 is null OR forms![filtered search]!Combo156

Thanks for the reply. I entered what you suggested and I got the following error;

"This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

 

[CJ_London]

OK - without having the context it's not possible to advise but for it to work,

your form 'filtered search' needs to be open
this will have been posted into a query as a criteria

 forms![filtered search]!Combo156 is null OR forms![filtered search]!Combo156

your form is called 'filtered search'
and it has a control called Combo156

 

[randallst]

OK - without having the context it's not possible to advise but for it to work,

your form 'filtered search' needs to be open
this will have been posted into a query as a criteria

 forms![filtered search]!Combo156 is null OR forms![filtered search]!Combo156

your form is called 'filtered search'
and it has a control called Combo156

It works fine on one criteria, but when you do 2 or more it brings up the previous error.

 

[CJ_London]

suggest you post the sql you are using or a screenshot of the query grid - if it works for one then, subject to the adjusted conditions for other fields/comboboxes, it should work for the others