Paasing parameters to a query

[ImoBase]

I am having a problem passing information to a query from VBA.

I can pass criteria that is simplistic e.g.

strAttending = "Attended"
SetResultAttending strAttending (this passes my information to a global variable)

And I then get a query result returned based on this criteria, no problem, with a GetResultAttending method in the query. Success.


But when I try to add more than one criteria, I can't get the code working. The query return is blank.

E.g. VBA


strAttending = """" + "Attended" + """" + " Or " + """" + "Confirmed" + """"

translates to:

"Attended" Or "Confirmed"

...in the query criteria box, in the query and this does not work. I have also tried passing it without the quotation marks without any success.

I have also tried the query criteria in another query that is identical, without passing variables, and the criteria returns the result I am looking for when I do that.

Any thoughts appreciated.

 

[pr2-eugin]

Can you show us your query? Or explain a bit more of what you are actually trying to achieve.. Probably the code where you are passing the argument..

 

[ImoBase]

Thanks for the reply, Paul.

I have a form that specifies the search criteria in a query.
It passes info to a global variable that can be accessed by the form (setting the variable) and the query (that can get the variable for the search criteria)

In a global module I have written the following code that allows me to set up a global variable (sent from the form) and a function that is accessed from the query (in the query search criteria, i have placed this code => GetResultAttending() )








Option Compare Database
Option Explicit

'********** GLOBAL VARIABLES *********'

Private strAttendingStatus As String

'*************************************


Public Sub SetResultAttending(Value As String)

strAttendingStatus = Value

End Sub



Public Function GetResultAttending() As String

GetResultAttending = strAttendingStatus

Debug.Print (GetResultAttending)

End Function

 

[pr2-eugin]

Okay.. so I believe that the Query will look something like..

SELECT * FROM someTable WHERE someField='Attended';

Right, but when you pass two values, it would be

SELECT * FROM someTable WHERE someField='Attended' Or 'Confirmed';

but it should actually be..

SELECT * FROM someTable WHERE someField='Attended' [B][COLOR=Blue]Or someField=[/COLOR][/B]'Confirmed';

 

[spikepl]

Parameters of a query can be values only. You are trying to pass language elements like "OR". That is no go. You are feeding a string as a parameter to the query, but the OR is not a string.

You can construct the entire SQL on the fly, with all the required OR's or an IN. (as in MyValue=IN(value1, value2, value3...)

 

[ImoBase]

Thanks for explanation, Spike.

SQL on the fly? I'm not sure how to do this within a query, controlled by a form. Could you point me to a tutorial or explanation?

 

[pr2-eugin]

Hope this helps ImoBase

http://www.fontstuff.com/access/acctut17.htm

 

[ImoBase]

Thanks both. Didn't know I couldn't pass conditional operators (very good to know) and the tutorial worked a treat.

Appreciated.

 

[Never Hide]

Sorry to pop up like this, but I'd like to thank pr2-eugin. That link you provided gives a step by step instructions to help me do exactly what I want to do

I just love this forum

 

[pr2-eugin]

You are most welcome Never Hide.. Glad to help..