Using global variable within query design criteria (1 Viewer)

Derek · Apr 12, 2018

Hi Guys

I am using a global variable called 'GCampId' within code that holds 'Campaign ID' value. Now I want to use this value to filter my access query. I have created a query in design view and when I try to se the critaria of 'CampaignID' field to GCampID and run the query then it gives me an error message.

Can anyone pls help me in this?

Thanks

Gasman · Apr 12, 2018

Why not just use the value in the design view?

Galaxiom · Apr 12, 2018

One way is to create wrapper function in a Standard Module and use the function in the query criteria.

Another is to pass it as a parameter to the function using VBA.

However CampaignID doesn't sound a likely candidate for a Global variable to me.

arnelgp · Apr 12, 2018

Yes a function like:

Global GCampID As Variant

Public Function getCampaignID()
getCampaignID= GCampID
End Function

On your query the criteria would be:

getCampaignID()

Frothingslosh · Apr 12, 2018

You can also reference the control of a form directly.

Another option is to use a TempVar, as, unlike global variables, TempVars can be accessed directly by queries.

However, I agree with Galaxiom: CampaignID doesn't seem like something you'd need saved in a global variable. What you're describing sounds more like something that should either just be passed as a named parameter or referenced directly via Forms!FormName!ControlName.

CedarTree · Apr 12, 2018

Feeding off this post, since my question is similar...

I have a criteria, called "team" and a get function fnTeam() = Team so I can reference in a saved query (where condition = fnTeam()). When Team = TeamA, works great or when Team = TeamB. But I want to also be able to choose all teams, so I set Team = "Like '*'" but in that case I get no returned records. If I created the query in code, I think it would work but trying to just use saved queries. Any suggestions? Thanks!

arnelgp · Apr 12, 2018

You can modify your critetia. Supposed you want all team, set the global var to "All". Then on your criteria:

Iif(fnTeam()="All", [Team], fnTeam())

CedarTree · Apr 12, 2018

Nice - I'll try it.

MarkK · Apr 12, 2018

Derek said:
I have created a query in design view and when I try to se[t] the crit[e]ria of 'CampaignID' field to GCampID and run the query then it gives me an error message.

Can anyone pls help me in this?

Suggest you show the error description, show the SQL, and show the code.
hth
Mark

Using global variable within query design criteria (1 Viewer)

Derek

Registered User.

Gasman

Enthusiastic Amateur

Galaxiom

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Frothingslosh

Premier Pale Stale Ale

CedarTree

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

CedarTree

Registered User.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread