Query (2003)

Topic

New Reply

Hi,

I use query to build search menu base on several criteria. There are three search texts on the search menu, “date”, “name” “invoice #”. User may be only have one information or two, such as only have “date” or have “date” & “name”. How should I set up in the query? If I put each one on different row of crieria, query only take one of three criteria. If I put two at the same row, query will find the data that meet both crieria, if user only have one data it won’t work.

Thanks

Regards

Reply | Quote

Replies

Say that your form is named frmMenu, and that the text box for date is named txtDate.
Say that the date field in your table is named dtmDate.
Remove the criteria from dtmDate in the query.
In the first blank column, enter the expression

[Forms]![frmMenu]![txtDate]

In the first Criteria row, enter

[dtmDate] Or Is Null

Do the same for the name and the invoice#, with the appropriate names substituted.

Reply | Quote

Thanks. But I would like to confirm that I really do the correct. Should the query looks like the following:

Thanks

Reply | Quote

Not quite.

In the first place, there shouldn’t be quotes around [dtmDate].

In the second place, you should use the name of YOUR form instead of frmMenu, and the name of YOUR text box instead of txtDate, and the name of YOUR date field instead of dtmDate.

In the third place, the second column shouldn’t refer to the date text box and field, but to the name text box and field, and the third column should refer to the invoice# text box and field.

Reply | Quote