Using global variable within query design criteria

[Derek]

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]

Why not just use the value in the design view?

 

[Galaxiom]

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]

Yes a function like:

Global GCampID As Variant

Public Function getCampaignID()
getCampaignID= GCampID
End Function



On your query the criteria would be:

getCampaignID()

 

[Frothingslosh]

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]

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]

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]

Nice - I'll try it.

 

[MarkK]

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