problem with parameterized queries

[evilman]

Hi,

I am capable of sending parameters to my query with a global VBA function. However, the problem is that the comparison operator cannot be changed.

Everytime the query is executed, the operator has to be changed (the selection is performed in a form).

SELECT * From MyTable WHERE Column = 5;
SELECT * From MyTable WHERE Column <= 9;
SELECT * From MyTable WHERE Column > 10;
etc etc...

I've tried to change the query to something like this:
SELECT *
FROM MyTable
WHERE Column VBAFunction();

the return value of VBAFunction() is the comparison operator plus the value ("<= 9").

But I get a syntax error..

any ideas?

 

[AncientOne]

Try calling the function first and assign the return value to a variable. then you can use:

SELECT *
FROM MyTable
WHERE & strMyReturnValue

 

[evilman]

hmm,
well I forgot to mention that this query is not a string in a vba module.
its a regular query.

 

[Fizzio]

What are you using the query for as if you are using it for a recordsource for a form or report, Ancient One's method will work fine

 

[AncientOne]

You can use the function name in the criteria line of a saved query. The return value will act as the parameter.

 

[evilman]

hi, thank you for answering.
well i'm using the query as a recordsource for a report.

well like I said, it wont work if I supply the operator in the return value of the function,

Say the return value of the global vba function is "<= 5".
access would interpret it as:
Column = "<=5"

so I need to dynamically change the operator of the where condition.

I'm currently using a dynamic query (using querydef to delete it and recreate it everytime I use it). But I dont like that method at all. it seems slow, plus debugging and adding features takes a lot more time than just going into the access query editor.

 

[Fizzio]

I would set the recordsource dynamically by using a created SQL statement, incorporating your operator choice from the form

Private Sub Report_Open()
me.recordsource = "SELECT fields FROM table WHERE " & _ 
"field " & Forms!FormName!OperatorControl & " value "

 

[evilman]

thanks I'm gonna try this.

 

[evilman]

I see, I always thought that the SQL query was saved as a text and reinterpreted everytime it was executed.
Thanks for the info.