I cant get the Global Variable to go into my Query

[ChampionDuy]

I have a query that needs to get the value of a GLobal variable. Here is the Query:


SELECT [CASEFILE].[CCN], [CASEFILE].[PERUNIT], [CASEFILE].[LOCATE], [CASEFILE].[CRIME], [CASEFILE].[APPROVE], [CASEFILE].[AUTH], [CASEFILE].[EXPDATE], [CASEFILE].[EQUIP1], [CASEFILE].[EQUIP2], [CASEFILE].[TYPE], [CASEFILE].[COMMAND], [CASEFILE].[EVAL1], [Casefile].[Eval1_2], [CASEFILE].[EVAL2], [Casefile].[Eval2_2], [CASEFILE].[EVAL3], [Casefile].[Eval3_2], [CASEFILE].[EVAL4], [Casefile].[eval4_2], [CASEFILE].[RESULTS], [CASEFILE].[STATUS]
FROM CASEFILE
WHERE CASEFILE.CCN=puRepCCN and Casefile.ccn IN (Select * from CCNRep);


The puRepCCN is a Global Variable and when this query is run it always brings up a parameter box and asks for the puRepCCN value. But when I click the button that invokes this query, puRepCCN is given a value and it should be carried to the query. But for some reason it is not. What am I doing wrong? I already tried puRepCCN.Value and that didnt work either. Please Help

 

[David R]

Make a quick global function

Public Function Get_puRepCCN()
    Get_puRepCCN = puRepCCN
End Function

SQL and VBA don't speak the same language, so you have to translate sometimes. Now your last line becomes: WHERE CASEFILE.CCN = Get_puRepCCN() ...

 

[Jack Cowley]

You have to get the value in the variable through a function. Refer to the function in the query, not the variable.

Function MyFunction()
MyFunction = MyGlobalVariable
End Function

 

[ChampionDuy]

Thanks

Thank you very much for the help, It worked great.