querys with null

[Dreamstar_1961]

Hi I'm building a database, now the question I got is this, the query will
run from a form, no on this form I got a number of both drop down boxes ant
text fields now I know how to get the results required, as the code below
shows. I know that the first one is the drop down box and the second one is
the text field, and leaving either of them or both of the empty will return
the results.

Forms![CustomerQry]![Customer_Cm] Or Like Forms![CustomerQry]![Customer_Cm]
Is Null

Like "*" & [Forms]![CustomerQry]![Draw_tx] & "*" &
[Forms]![CustomerQry]![Draw_tx] Is Null

(example)
Customer - enter customer name from drop down box or leave blank
Drawing - Enter drawing No/Name full or part , or leave blank

If either, or something in either of these, they return only those records,
both have nothing in them return all records. that part worked fine. the next
part the form was updated to include.

Customer - enter customer name from drop down box or leave blank
Drawing - Enter drawing No/Name full or part , or leave blank
Products - Entered from drop down box or leave blank\

and the query has had that below added,

[Forms]![CustomerQry]![Products_CM] Or Like
[Forms]![CustomerQry]![Products_CM] Is Null

but to add that the query, the query has to be remade from scratch as
anything added after the query was fisrt run will not workand retunrs that
the query is to complex. any idea on how to update the query without having
to rewrite the whole thing again.

Thanks

 

[Allen Browne]

The "Like" may be causing the "query too complex" error. You could write it
as:
([Forms]![CustomerQry]![Products_CM] Is Null) OR
([SomeField] = [Forms]![CustomerQry]![Products_CM])

But if you have several of these fields, it will probably be easier and more
efficient to leave the criteria out of the query. Instead, build a Filter
string on your form, using only the boxes where the user actually entered
criteria.

There is an example of how to build such as form in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html