Creating a dynamic Query

[MAPetry]

I need to create a dymanic query. HELP!!!
I have a form where the user has the option to input 5 different paramaters. I want to create a query so they can select 1 or all five and the correct data will be returned. I don't know exactly how to do this but am guessing that I need to define a recordset with an underlying where clause to obtain the information i want and then base the report on the defined recordset? ANy help or guidance would be appreciated.

Mary Ann Petry

 

[THoey]

Sounds like you are going to have to use VB to code this. Have a form with the 5 parameter fields and a "Run Report" button. Once they fill in their information, have the OnClick event of the button call a function passing the five values. In the function, build your SQL string WHERE clause with any of the parameters that are not = NULL:

Dim db As Database
Dim rst As Recordset
Dim strSQL as String

strSQL = "SELECT * FROM MyTable "
If Param1 <> NULL THEN
Add on to query here...
EndIf
If Param2 <> NULL THEN
Add on to query here...
EndIf
...

Then, you can run the report using the created SQL string.

If you build the form and give us the forms control names and the table\field names, we can probably help you with more of the VB portion of it...
Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...