CODEWITHSUNDEEP
ms-accessvba
JamTay317
JamTay317

Reputation: 1017

DSum with tempvars returning error

I have some VBA code that sets a TempVar:

TempVars!ThisQtr = quarter
TempVars!LastQtr = lastQuarter
TempVars!LastYr = lastYear
TempVars!ThisYr = currentYear

Great! I know that it does set it as I have checked multiple times.
now my problem:
I'm trying to use this in a query

DSum("[SumBase]","CompareUnionQuery","[AU] = '" & [AU] & "' AND [GRP_ID] = " & [GRP_ID] & " AND [ACCOUNT] = '" & [ACCOUNT] & "'" & " AND [Fiscal_Year] = " & [TempVars]![ThisYr] & " AND [QTR]= " & [TempVars]![ThisQtr])

which this does work when I change out the TempVars with values. Can anyone help me with this? I have tried to put a single quote around them and that doesn't work.

enter image description here

this expression does work:

DSum("[SumBase]","CompareUnionQuery","[AU] = '" & [AU] & "' AND [GRP_ID] = " & [GRP_ID] & " AND [ACCOUNT] = '" & [ACCOUNT] & "'" & " AND [Fiscal_Year] = 2015 AND [QTR]= '3'")

Upvotes: 1

Views: 76

Answers (1)

HansUp
HansUp

Reputation: 97101

Compare the last pieces of your DSum expressions ...

  1. AND [QTR]= '3'")
  2. AND [QTR]= " & [TempVars]![ThisQtr])

You reported #1 works and #2 triggers the "Data type mismatch" error. So add single quotes before and after the TempVar value ...

AND [QTR]= '" & [TempVars]![ThisQtr] & "'")

Upvotes: 1

Related Questions