global variable in a query

hello, i want to assign the year in a variable when my project opens and then use that throughout the entire project.
for example:
dim dPresentYear as date = 2015
dim dPreviousYear as date = 2014
then use these variables in a query in the criteria section
presently, i use this:   [Enter Year of hours in this format YYYY]
by having a global variable i can substitute the global variable and not ask for the year each time the query runs.
so, is it possible to assign a variable in the model section and the reuse it throughout the project.
thanks in advance
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

define these two variables as Public (not Dim) in a module.
cazinkAuthor Commented:
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"

Open in new window

In a query, you would use string concatenation with delimiters
dbEngine(0)(0).Execute "Select * From tablename Where strMemberBadgeStatus ='" & strMemberBadgeStatus & "'"

Open 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 & "'"

Open in new window

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

Open in new window

Which makes the SQL look like this:
dbEngine(0)(0).Execute "Select * From tablename Where MemberBadgeStatus =" & strMemberBadgeStatus

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

cazinkAuthor Commented:
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()

Open in new window

TempVars("MemberBadgeStatus") = "Active"

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

Open in new window

cazinkAuthor Commented:
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.
You're not referring to the examples I gave you, are you?

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.


Edit.  I'm referring to what you said about putting the variable in the General Declarations section of a form.
cazinkAuthor Commented:
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?

Open 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.
Dale FyeOwner, Developing Solutions LLCCommented:
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()

Open 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

Open 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]

Open 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.

You've made my week by introducing me to the Tempvars object.  Although not as powerful as a dictionary object, it does facilitate store-and-forward passing of data to queries and macros.  Thank you.

Since this solution hasn't been mentioned, I thought I'd offer it now...
You can create a table to hold values.  I usually see two column tables, containing the name:value pairs.  However, I've also seen some tables with three or more columns, allowing you to specify the data type of the value, a description of the item, and a default/preferred format of the item's data.

You can include these values in queries by a columnar select, within parentheses -- like you'd do if you were creating a column in the Select clause.
You can also include these values with a DLookup() function -- probably the simplest
You can also include these values with a user-defined function -- what you'd need if you had a fancy 3+ column table.
Another method is to use a form to hold the values.  I started doing this 20 years ago because global variables were flaky and creating functions to return them so I could use them in queries was a lot like work.  This was long before Tempvars were available.  The login form opens the hidden variables form and throughout the app, I use that form for "remembering" session state values and for passing arguments to reports.

The biggest advantage of the form (and why I haven't switched to TempVars) is the ability to make the form visible during testing and watch values as they change.  Although you can set the tempvars in the immediate window, you have to remember their names and the syntax is quirky.  The form method helps those of us who couldn't remember their own name without a nametag.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:

I used to do it that way myself.  I'm sure you already know this, but for others, you can get a list of your tempvars like:

for intloop = 0 to tempvars.Count-1 : debug.Print tempvars(intloop).Name, tempvars(intloop).Value:next

I stuck with my method because I didn't find any particular advantage to switching to TempVars and I don't have the patience for "change for the sake of change".  Plus as a consultant, I end up developing in whatever environment the client wants so my form will work all the way back to 1.1 (not that anyone is using that version any more).  TempVars solved a nagging problem developers had when using public variables so it was an excellent addition to the language and I recommend their use to anyone who hasn't already solved the problem in a coherent manner.  I like my method because I can group like items into sections which makes it easier to see that I have already defined a variable for "x" and I mentioned my technique because it transcends versions and there may be someone out there struggling with a pre-A2007 version who can't use TempVars.
cazinkAuthor Commented:
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.
cazinkAuthor Commented:
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.
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.

Dale FyeOwner, Developing Solutions LLCCommented:

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).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.