Error 3070 using TempVars in Query

[mistera]

I've searched several forums but cannot find a resolution to my issue. I'm using Access 2007 and have a query in which I have a Where clause that includes a TempVars variable. Here is the SQL:

SELECT tbl_CostCenter.*, tbl_OpEx.*, qry_OpExByCC.*
FROM (tbl_CostCenter INNER JOIN tbl_OpEx ON tbl_CostCenter.[Cost Center] = tbl_OpEx.[Cost Center]) INNER JOIN qry_OpExByCC ON tbl_CostCenter.[Cost Center] = qry_OpExByCC.[Cost Center]
WHERE tbl_OpEx.[Base Year]=TempVars!CurrentBaseYear
ORDER BY tbl_CostCenter.[Cost Center];

I get runtime error 3070 stating that "The Microsoft Access database engine does not recognize 'TempVars!CurrentBaseYear' as a valid field name or expression." When I run the query without the Where clause, it runs fine, except it includes all years, not just the one I need.

However, when I use the immediate window to check the value of TempVars!CurrentBaseYear, I get a result of 2012 which is what I expected. So how can it not be recognized in the query? I thought that one of the perks of using a TempVars variable is that it can be used in queries.

Does anyone know what the issue could be?

 

[CJ_London]

You need to create the sql in vba if you are referring to a vba variable - you can then assign the sql string to a querydef

i.e.
"WHERE tbl_OpEx.[Base Year]=" & TempVars!CurrentBaseYear

The only way to have a query reference a vba value is through a public function in a module

i.e.

Function CBY() as Integer
    CBY=TempVars.CurrentBaseYear
End Function

And then in your query

WHERE tbl_OpEx.[Base Year]=CBY()

 

[mistera]

Awesome! I created the function and used it in the query as suggested and it works like a champ. Thanks so much!!

 

[atrium]

I have had the same problem

I created the Module as

Function LM() as Integer
LM=AddMatterFrm.[MatterIdFld]
End Function

and the query is

 SELECT DebtTransactions.DebtTransId, DebtTransactions.MatterId, DebtTransactions.TransCode, DebtTransactions.TransDate, DebtTransactions.Amount
FROM DebtTransactions
WHERE DebtTransactions.MatterId=LM()
ORDER BY DebtTransactions.TransDate;

I get the following error

Run time error '3075'
Ambiguous name in query expression
'DebtTransactions.MatterId=LM('

NOTE the last character the LM() has been cut off