Query Table using Multiple Criteria - User Choices 2

[bigrogCSC]

I basically want to create a search form with all the possible ways to query a particular table allowing the user to narrow the search results returned by adding additional criteria... ie.. Pick either all Contracts for a particular customer... or all contracts for a particular customer after a specified date... at the user's discretion. The search screen would have multiple choices...

I can do simple parameter queries, but I can't seem to figure this one out. Can someone help.

 

[THWatson]

bigrogCSC
Here's one approach...

1. Create an unbound form. Let's call it frmSearch.
2. On that form put a text box for each criteria for which you want to search. For example, one text box for the Customer, one text box for the StartDate, one text box for the EndDate. Let's call those txtCustomer, txtStartDate, txtEndDate.
3. In the query, in the Customer column, put the following as criteria...
Forms!frmSearch!txtCustomer
4. In the query, in the contract date column, put the following as criteria...
Between Forms!frmSearch!txtStartDate AND Forms!frmSearch!txtEndDate
5. On the form frmSearch, also put a command button to Preview the report, and a second one, if you wish, to Print the report.
6. Base your report on the query, but drive it from the command buttons on form frmSearch.

If you want the ability to select all customers between two dates, change the criteria in the Customer column to...
Like Forms!frmSearch!txtCustomer & "*"

Hope that helps.

Tom

 

[Lightning]

bigrogCSC

I use a search form to do this in many of the databases I build. I've used it with anything up to 10 parameters.


HTH
Lightning

 

[THWatson]

Try changing the criteria to this...

Between (Forms!frmSearch!txtStartDate OR Like "*") AND (Forms!frmSearch!txtEndDate OR Like "*")

Tom

 

[PHV]

Between Nz(Forms!frmSearch!txtStartDate, [date field]) And Nz(Forms!frmSearch!txtEndDate, [date field])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244