Store a query as variable 2

[Freemo]

Hello

I have been trying for ages to have a typed in query to be stored for later use then variables to be included.

For example:

SELECT * FROM table WHERE {List} = '{SEARCH}'

I have made an application which allows users to write there own queries without the need to use access. The query is then stored in a database and then called for on a later date. The fields {List} & {Search} are the criteria which then would be filled from textboxes.

But anyway i try it when the query is run, it is exactly as it is above.

Any help would be muchly appreciated.

Thank you very much

 

[HarleyQuinn]

How about something like:

strYourSQL = "SELECT * FROM table WHERE " & Text1.Text & " = '" & Text2.Text & "'"
Debug.Print strYourSQL

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Freemo]

Yes this is the way i tried but the problem is found when i call the query back from a database, i have to store the query as a variable but when ran it looks like this:

SELECT * FROM table WHERE " & Text1.Text & " = '" & Text2.Text & "'"

It doesn't fill the textboxes with anything, it just tries to search for " & Text1.Text & ".

 

[HarleyQuinn]

Why not store the value of the variable that you created from the query? E.g. the one you would see in the debug.print statement in my previous post?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Freemo]

I am having problems with the calling back the query as the variables might change later on say one of the fields is a date field, this when called back could be changed to todays date. I'm kinda sure it is a the calling the query back from the database which is causing me the problem. I am storing it as a string. I'm not too sure been trying different methods for days now.

Thanx for your help so far HarleyQuinn

 

[Andrzejek]

What about storing your query and your values from Text1 and Text2 in your db?

Then, when you retrieve it, you would have:
a = rst!value1
b = rst!value2
sql = SELECT * FROM table WHERE {x} = '{y}'

You can just
sql = Replace(sql, "x", "a")
sql = Replace(sql, "y", "b")

and you have
sql = SELECT * FROM table WHERE {a} = '{b}'

HTH

---- Andy

 

[Freemo]

That makes perfect sense and works nicely. Thank you Andrzejek. And Thank you HarleyQuinn for your quick responses. Stars all round.

 

[BobRodes]

One more thing: it's more secure to use stored procedures, and pass parameters to them, then it is to use this method. You might want to read up on SQL injection attacks; as I recall, chiph has an excellent FAQ on the subject. I'm sorry, I can't find it just now.