criteria problem

[David b]

I have a query which gets its criteria from a form. On the form is a combo
which selects cust number and a text box selecting date.
This works fine if both have data or both are null.
Is there a way to have 1 with data and 1 null. -- So I can see all the
customers with activity on 11th March or I can see all of Mr White`s activity on
any day

SQL follows

SELECT SundrySaletbl.eventDate, SundrySaletbl.customerid,
[Forms]![SundrySalefrm]![evdate] AS Expr1, [Forms]![sundrysalefrm]![selectcust]
AS Expr2
FROM SundrySaletbl
WHERE (((SundrySaletbl.eventDate)=[Forms]![SundrySalefrm]![evdate]) AND
((SundrySaletbl.customerid)=[Forms]![sundrysalefrm]![selectcust])) OR
((([Forms]![SundrySalefrm]![evdate]) Is Null) AND
(([Forms]![sundrysalefrm]![selectcust]) Is Null));

TIA
david b

 

[Jon K]

SELECT [eventDate], [customerid]
FROM SundrySaletbl
WHERE IIf([Forms]![SundrySalefrm]![evdate] Is Null, True, [eventDate]=[Forms]![SundrySalefrm]![evdate]) And IIf([Forms]![SundrySalefrm]![selectcust] Is Null, True, [customerid]=[Forms]![SundrySalefrm]![selectcust]);


Alternatively you can use:-

SELECT [eventDate], [customerid]
FROM SundrySaletbl
WHERE ([Forms]![SundrySalefrm]![evdate] Is Null or [eventDate]=[Forms]![SundrySalefrm]![evdate]) And ([Forms]![SundrySalefrm]![selectcust] Is Null or [customerid]=[Forms]![SundrySalefrm]![selectcust]);

However, if you subsequently edit this query in Design View, Access will re-arrange the Where Clause of ther statement and add lots of ANDs and ORs, making it difficult to read or to add other criteria.