How to define a MS Access global variable using a sql string

Question

First, I have searched for an answer to this question but all searches have found answers to how to use a global variable in a query. I know how to do that.

What I want to do is create a query object which will call UseRates that are being calculated as data is changed - this is simple. The unknown answer is how to call this query (or input the results of this query) into a global variable such that the global variable definition would look something like (name of the query object is qryUseRates):

Public gUseRate1 = "SELECT qryUseRates.UseRate FROM qryUseRates WHERE lngAgeGrp = 1"

Then, of course define 4 more global variables similarly for the other 4 use rates (changing the where clause for each).

The reason: the use rates are calculated with all geographical areas aggregated but then they are applied to each geographical area separately. Thus, qryUseRates returns 5 rows but the query that uses these rates have close to 100 rows such that I don't see a way to join the 2 queries.

Just a follow up; Once the variable is defined I would do:

Public Function GetUseRate1()
getuserate1 = gUseRate1

Then go to a new query object and wherever I need it, I would use GetUseRate1(). When a global variable is properly defined, this seems to work - tried it just using something simple to define the global variable.

So your question is How do I run a query and read the resulting field into a variable?? (Actually, you probably should read into an array, so you can keep all variables together, but you can use five separate variables.) If that's not what you're asking, then I'm afraid I can't make sense of what you wrote. — Ken White
– Ken White, Commented Mar 25, 2016 at 2:38
Ken, That is what I am looking for. Thanks, I see that Gustav has it with DLookup(). — random13
– random13, Commented Mar 25, 2016 at 17:19

Gustav · Accepted Answer · 2016-03-25 09:17:44Z

0

Use DLookup:

Public gUseRate1 As Currency
gUseRate1 = DLookup("UseRate", "qryUseRates", "lngAgeGrp = 1")

If "not found" may be expected:

gUseRate1 = Nz(DLookup("UseRate", "qryUseRates", "lngAgeGrp = 1"), 0)

answered Mar 25, 2016 at 9:17

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

random13 Over a year ago

Exactly! Thanks a bunch. I started down the path of DLookup() but then turned to Global Variables before I got the answer I needed since Global was needed for another use. I like them both and now have them in my arsenal. Thanks again.

Collectives™ on Stack Overflow

How to define a MS Access global variable using a sql string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related