How to call a Public variable in a query?

[latex88]

I want to be able to, when a certain button is pushed, pass a variable to a query. These buttons would be utilized in several forms as subform so I can just code the event procedures once.

I think storing a value to a public variable is fairly easy, but I’m not sure how, in a query, to call that variable as a criteria. Please help.

 

[AncientOne]

You can use a procedure name in a query. Therefore, you could assign the variable to a procedure name.

 

[Mile-O]

In a module...

Public Function OnlyForQuery()
    OnlyForQuery = YourPublicVariable
End Function

Then use OnlyForQuery() in your query...

 

[latex88]

Your suggestion sounds simple enough and I follow the logic, but somehow it's not working properly.

In a form, I have a procedure that assigns the value to a pub variable. Then I have a function, as you suggested, with its name = to the variable.

Then I inserted that function name in the criteria line of the query but it does not work. What do you think I am doing wrong?

I know I assigned it properly as I inserted a msgbox to display the value when the button is pushed. Beyond that I'm not sure where the problem may be.

 

[latex88]

This is what I got...

Declaration Under Module...
Dim strCategory As String



the event procedure of the button...

Private Sub cmdMenuCategory_Click()
strCategory = "Chicken"
End Sub



The function...

Public Function CategoryCall()
CategoryCall = strCategory
End Function

In the criteria line of the query...

CategoryCall()

 

[Mile-O]

Try as your criteria:

=CategoryCall()

 

[AncientOne]

I also think that has to be :

Public Category as string.....

 

[latex88]

thanks a lot. I got it working after I change to Public strCategory as String and changed the event procedure to Public sub cmdMenuCategory_Click()

 

[Mile-O]

Never spotted the Dim statement in the module.

 

[teel73]

I am having a very difficult time with such a simple task. I simply want to pass a public string variable that currently holds the value of "1 or 2" to my query as criteria for which questions to return. So here's what I have.

Public wrongAnswerCriteria as string

wrongAnswerCriteria is set through some other code in my project from a form. Where [quest] is a variable that holds the value of the current question number from the form. Below is the code:

And for the sake of an example, lets say quest = 1 the first time and then 2 the second time.

If wrongAnswerCriteria = "" Then
                         wrongAnswerCriteria = quest
          Else
                         wrongAnswerCriteria = wrongAnswerCriteria & " Or " & quest
          End If

ok. so wrongAnswerCriteria = 1 Or 2 .. This is what I want as my criteria in my query. I want to return all questions where the question number is 1 Or 2.

so. I have a public function which is below:

Public Function wrongCriteria() As String
wrongCriteria = wrongAnswerCriteria
Debug.Print wrongCriteria
End Function

When I run the debug.print wrongCriteria, it prints 1 Or 2 .. just what I want. But in my query on the criteria line for the field [questions] ... I put this: wrongCriteria()

And my query returns nothing. 0 records. But if I manually type on the criteria line 1 Or 2 the query works. I haven't the slightest idea why this isn't working. Can anybody make heads or tails out of this. I've struggled with this for 2 days now. Very frustrated .. someone please help me.

 

[DCrake]

try this link

 

[teel73]

I have my public variable and functions set up the same way ... I can't figure out why mine isn't working.

 

[SteveSchapel]

Latex,

If you are using Access 2007 or Access 2010, you can use a TempVar, which is the simplest solution.

 

[teel73]

Latex,

If you are using Access 2007 or Access 2010, you can use a TempVar, which is the simplest solution.

Its not working I have no idea what I'm doing wrong. when I set my variable to 1 value it works but if it has more than 1 value of criteria it displays nothing .. For example .. if i ask my query to return questions where question# = 1. Then that works fine .. but when the criteria is 1 or 2 or 3 .. it doesn't recognize the variable. I don't know why. Should the variable be a integer or something? I have it set as a string.

 

[SteveSchapel]

Teel,

A TempVar can be any data type. But it (like any variable) can only hold one value at a time. Do you mean you are trying to get the query to return:
Where QuestionNo = "1 Or 2 Or 3"
If so, well no, that won't work. There is no question where the number is "1 Or 2 or 3". It has to be:
Where QuestionNo = 1 Or QuestionNo = 2 Or QuestionNo = 3

 

[SOS]

It has to be:
Where QuestionNo = 1 Or QuestionNo = 2 Or QuestionNo = 3

Or

Where QuesitonNo In(1,2,3)

 

[SteveSchapel]

Or
Where QuesitonNo In(1,2,3)

LOL! Well, true, but in my efforts to illustrate the concept, I had deliberately avoided mentioning this syntax, for fear that Teel might then think we could create a single variable "1,2,3" and go:
Where QuesitonNo In(TempVars!VariableName)
... which, for the record, won't work.

 

[SteveSchapel]

Teel,

By the way, notice we have changed your Question# to QuestionNo, due to the fact that it is inadvisable to use a # as part of the name of a field or control.

 

[SOS]

Sorry, wasn't thinking.