Multiple Search Criteria Blank - All records returned

[blade6247]

Hi, I'm using a form with multiple search criteria, some or all of which can be left blank.

When all fields are left blank, the query returns all records. However, there are likely to be several thousand of these records in the future. How can I get the query to return NO records if all fields are left blank?

My query uses field = [forms]![formname]![controlname] or [forms]![formname]![controlname] is Null

Many thanks

 

[KenReay]

My query uses field = [forms]![formname]![controlname]

should do it



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Add this condition in the WHERE clause of the generated SQL code:
AND (Trim([forms]![formname]![controlname1] & [forms]![formname]![controlname2] & ... & [forms]![formname]![controlnameX] & "") <> "")

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

 

[FancyPrairie]

Check out this FAQ FAQ181-5497 It will return the where clause (without the word where). It works for 0 to many single and multiselect list boxes, comboboxes, text boxes, ranges, options groups, and check boxes. You only have to do 3 things to make it work.

1. Open a new module and copy and paste the function into the new module.
2. Set the Tag property of your check boxes according to the directions in the FAQ.
3. Open the report as specified in the FAQ

However, in your case, store the results of the function in a variable. Then if the function returns a nullstring, no need to open the report. It would work something like this:

Dim strWhere as String

strWhere = BuildWhere (Me)
if (len(strWhere) > 0) then
Docmd.OpenReport "YourReport",acViewPreview,,strWhere
else
msgbox "too many records to report"
end if

 

[blade6247]

Thanks very much guys - working great now!