global variable in a query

define these two variables as Public (not Dim) in a module.

first dim was a poor choice i did know public
to make sure i am not having a syntax error with dates i and using something simpler
Public strMemberBadgeStatus as String
Set strMemberBadgeStatus = "Active"
now in a query in the criteria section how do i use the variable strMemberBadgeSatus
in i type it in quotes are placed around it as this "strMemberBadgeSatus"
if i type [strMemberBadgeSatus] then is acts as an input for a response.

You do not use "Set" for simple variables:

Public strMemberBadgeStatus as String
strMemberBadgeStatus = "Active"

Select allOpen in new window

In a query, you would use string concatenation with delimiters
Example:

dbEngine(0)(0).Execute "Select * From tablename Where strMemberBadgeStatus ='" & strMemberBadgeStatus & "'"

Select allOpen in new window

Although, I'm surprised you have a column named strMemberBadgeStatus
I would expect to see:

dbEngine(0)(0).Execute "Select * From tablename Where MemberBadgeStatus ='" & strMemberBadgeStatus & "'"

Select allOpen in new window

If you want to simplify your SQL, you can include the string delimiter (apostrophe) characters with the value assignment:

strMemberBadgeStatus = "'Active'"

Select allOpen in new window

Which makes the SQL look like this:

dbEngine(0)(0).Execute "Select * From tablename Where MemberBadgeStatus =" & strMemberBadgeStatus

Select allOpen in new window

i am not usually writing in SQL mode but in the query design mode.
WHAT is the syntax i am placing in the criteria line under the field name i choose in the query design mode.
i mentioned query design mode and not the SQL view.
is this possible

If you want to construct a query with the query builder, you would need to put the Public variable declaration in the General Declarations section of a form.  Such public variables act like properties of the form and are accessible with the Build dialog.

Such public variables act like properties of the form and are accessible with the Build dialog.

@aikimark, this is new to me.  Can you explain how you accomplish this because I couldn't get that to work.

@cazink, the way I access a global variable in a query is either to create a function that returns that variable or to use TempVars instead.

Public Function GetMemberBadgeStatus() As String
    GetMemberBadgeStatus = strMemberBadgeStatus 
End Function

'Criteria you would use in query design would be GetMemberBadgeStatus()

Select allOpen in new window

TempVars("MemberBadgeStatus") = "Active"

'Criteria you would use in query design would be TempVars!MemberBadgeStatus

Select allOpen in new window

Ron

i took a quick look at this and will have to verify if this is another way to skin the cat. it seems on the surface that this is also not a solution, the value or the variable does not seem to be accessible. so the public or any type of constant or variable declaration in a module is not usable in the rest of the project in forms or any query.
again the simple task is to set something to a value in one place to use throughout the project so the user does not have to answer an input or have to assign it over and over again in queries etc..
thanks for your help, will have to think of another solution.

@cazink,
You're not referring to the examples I gave you, are you?

Ron

Do you have a form?
If so, move the Public strMemberBadgeStatus as String statement into its General Declarations section
In the Form's Open or Load event, move the value assignment statement.

When you are in the query builder, right click in the criteria cell and choose Build.  I think you should be able to reference the strMemberBadgeStatus (quasi-) property of the form from within the dialog.

Alternatively, you can create a Public function in a module that will return the value of the strMemberBadgeStatus variable.

A good name for the function would be GetMemberBadgeStatus

@aikimark, have you tried that?  I tried it and couldn't get that to work.

Ron

Edit.  I'm referring to what you said about putting the variable in the General Declarations section of a form.

no, my question was simply...
can i declare a variable in a module...
give it a value...
then use that variable in a query as a criteria.
to eliminate having to enter an answer to an input

@cazink, I'm not following.  Have you tried either example I gave in this post?

@Irogsinta

[Forms]![FormViewer].strTestProperty

Select allOpen in new window

It worked in the code window, so I thought it would work in a query.  Unfortunately, it doesn't.

Will have to wrap the value in setter/getter routines, as already shown above.  I tried adding a form property, but that didn't work. :-(

Thanks for testing this.

There are several ways to do this, all of them have been mentioned above, and all work.

1.  Declare the public variable, set the value, then use a function to call that value in your query:

Global intPresentYear as integer  'I would use global rather than public

intPresentYear = Year(Date())

Public Function fnPresentYear() as integer
    fnPresentYear = intPresentYear
End Function

SELECT * FROM yourTable where Year([DateField]) = fnPresentYear()

Select allOpen in new window

2.  Refer to the actual control on your form (if you have a form control which contains this value)

SELECT * FROM yourTable WHERE Year([DateField]) = Forms!yourFormName.txt_PresentYear

Select allOpen in new window

3.  Using Tempvars, my preferred method because they will:
     a.  not lose their values when you encounter unhandled errors
     b.  you can set their values in the immediate window and test your queries without having to run the application

Tempvars("PresentYear") = Year(Date())

SELECT * FROM yourTable WHERE YEAR([DateField]) = [Tempvars]![PresentYear]

Select allOpen in new window

Although there are several ways to refer to tempvars in VBA, the only effective way I have found to refer to them in a query is to use the brackets as shown above.

HELLO ALL,
first let me apologize for not realizing that i was responding to multiple replies. it was late and i was concentrating on multiple tasks. I will have to sift through the comments and decide an action to take. i have thought of using a table as i have done this in the past and the dLoopUp works for me. however, the hidden form also is intriguing. the fact that i can use this to pass info to reports is also an avenue that i will look into. the use of creating classes as they do in VB is also an area i would really like to explore but need more direction on proper implementation.
in closing i really appreciate the effort put forth.

just a note to those who respond to us questioners, remember that what makes sense to you may not to us, try to answer the question and not just part of the question, make sure that what you recommend actually works.  if we are using query design and not SQL then do not give us a response using SQL.
PS i having been using EE for many years, however do not use it enough to know the correct procedure for assigning points so i did my best.
AND IN CLOSING FOR ALL OF YOU OUT THERE LOOKING FOR ADVICE.......THIS IS THE PLACE TO GO

remember that what makes sense to you may not to us

cazink, the way for us to determine the questioner's level of knowledge is by your response to our posts.  If you were already familiar with many concepts, it would be inappropriate to give you step by step instructions as if you were a beginner.  Now if we give an answer and you request for more guidance, of course the next response from us would include more details.   What I find strange is that you never seemed to respond to my questions.  It seemed like your answers were always addressed to aikimark.  I first brought up TempVars as well as using a function to return your global variable in this post before anyone else and I included examples on their use as well as the criteria you would use in your query design.  If what I posted wasn't clear to you, all you had to do was simply respond to me.

Ron

@cazink,

The other thing that you need to do is when you are responding to an expert, especially when you have multiple experts responding to you, you should start your response with who you are replying to (see above).