Dynamic query generation in Access

[venkiatmarut]

Hi,

I have a different problem. I couldn't get any clue on how to handle it.

I need to generate a query whose where clause string not fixed.
I.e. there is no key column and it should be generated by entering from user.



SELECT col1, col2, col3, col4 FROM tab1
WHERE col6 LIKE "SD*" & "200" OR LIKE "SO*" & "095" OR LIKE "PD*" & "124"


The actual col6 data is as follows_
SD 200
PD201
SO 067
SD038
.......


Here in the code "200", "095" and "124" are vaibale not fixed and "SD", "SO" and "PD" are fixed.

How can I prepare this query?

[CreganTur]

I.e. there is no key column and it should be generated by entering from user.


Are you trying to say that you want the query's results to be determined by a suer's selection? Will your user be making this selection from a Form, or would a simple parameter query work for you?

If this is not what you want, then please be more specific about what you are trying to achieve.

[venkiatmarut]

Are you trying to say that you want the query's results to be determined by a suer's selection? Will your user be making this selection from a Form, or would a simple parameter query work for you?

If this is not what you want, then please be more specific about what you are trying to achieve.

Randy,

Thanks for your reply.
I just want to know how do create this query in dynamic. Will it be possible if user enters those numbers into a text box, we prepare a total string and give that total where cluase as paremeter to query?

[CreganTur]

You can use the value of a textbox on a form to provide a parameter value to a query.

For the field that the textbox will be providing data for, in the criteria section, type in [Forms]![FormName]![TextBoxName] - repalce FormName with the form's name and TextBoxName with the name of the textbox that will hold the value you want.

Then, still in query design view, click on Queries -> Parameters and enter the exact same string and select the data type that should be returned. Then click okay.

Now, on your Form, you'll need to use the DoCmd.OpenQuery method to open your Select query. It will look at the textbox's value and use that as a parameter for the query. The form must be open for this to work.

HTH:thumb

[mud2]

xyz200 is not "like" 200. Use "*" & 200 & "*". That's Quote, asterisk, quoute.

If I remember right, this should work! Ihope so, 'cause I'm going to use it.

[OBP]

This code generates a Dynamic query for a Report using the QueryDef, you just need to incorporate the Like "*" & "*" mud talked about.

Dynamic Report Query

Private Sub Command2_Click()
Dim rs As Object, sql As String, qdfNew As Object
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories WHERE [CategoryID]> 10 ")
DoCmd.OpenReport "Categories Query", acViewPreview
End With
End Sub