I have a form in which the user sets criteria into text boxes, I want to display a subform/datasheet below showing the records which meet the criteria given in the text boxes.
There are 8 criteria boxes and 2 "date between" criteria so 14 text boxes which can be set.
I want the user to be able to input values into any, all or none of the criteria to show the relevant records in the datasheet (so if any criteria are left empty then all records should be returned for that field in the table).
The way I have done it so far is with a stupidly long WHERE clause which doubles every time I add a new criteria (since it has to list every possible combination of all 14 criteria either having a value or "is null").
Like I said; 14 criteria boxes. Ridiculusly long!
It was working fine until I got to the 8th criteria box when I got an error message basicly telling me that my SQL string is way too long.
Is there a better way to set multiple criteria for a form/datasheet? Or a way of allowing a longer SQL string?
Any help will be greatly appreciated!