Hello. I have a database table with a variety of ways they wish to query it. Rather then create a query for each way I was thinking of using a form to provide the input values for the query. The main problem I am having is how to handle null values from the form. Basically if they dont want to limit the query by say 'province', then they just leave it blank and its ignored as far as the query is concerned. Then check the 'county' and if they dont want to limit the query they leave it blank, and so on.

I was messing with null values and paramaters but can only get that to work with one field (put [enter province] in Criteria and 'Like [enter province] is null' in Or), if I do that for both province and county it never works properly.

What would be the best way to approach this? Suggestions appreciated.

Ken

SELECT
tblOne.Field1
, tblOne.Field2
, tblOne.Field3
, tblOne.Field4

FROM
tblOne

WHERE
(((tblOne.Field1) Like [Enter value for Field1 - Leave blank for all records]) AND
((tblOne.Field2) Like [Enter value for Field2 - Leave blank for all records]))
OR
(([Enter value for Field1 - Leave blank for all records] Is Null))
OR
(([Enter value for Field2 - Leave blank for all records] Is Null));

:cool: HTH

On the query button code, build a custom sql string based on the form text boxes?

or

I would just offer them an exported xls of the data filtered on a date range and let them hack away on it in excel...

kh


yeah - what HTH said...

Thanks HTH, I had to modify the WHERE clause, but you pointed me in the proper direction. Ended up with:

WHERE
((((tblOne.Field1) Like [Enter value for Field1 - Leave blank for all records]) OR ([Enter value for Field1 - Leave blank for all records] Is Null))
AND
(((tblOne.Field2) Like [Enter value for Field2 - Leave blank for all records]) OR ([Enter value for Field2 - Leave blank for all records] Is Null)));

Cul