Query auto-prompt question

[RickBerem]

Hello,
I link a query with a form in the way that the query returns records with the criteria written in the txtboxes of the form.

My problem is that the query will still prompt me if I write nothing in the text boxes.

What I'd like it to do is if there's something in the text box then it adds it to the query else it just bypass it...

so if you choose a date and a number but not a best before date the query will return all records with the good date and number but won't prompt for a best before date.

any ideas?
Thanks

RickBerem

 

[gwinn7]

Yes, one way I know is to not use a pre-defined query object.

Instead, build an SQL dynamically in your code and change the WHERE condition contents. If the text box is empty, simply do not include that condition in the SQL WHERE clause. After the SQL statement is built, apply it to the Rowsource and Requery/Refresh the records.

Does this help?

Gary
gwinn7

 

[hennep]

you can code it in a query like this:

SELECT field1
FROM table1
WHERE field1=IIf( IsNull([Forms]![Form1]![Text0]), [field1], [Forms]![Form1]![Text0] );