I have a custom function that returns the DSUM of a query field and want to use variables for the DSUM criteria.
This function works and returns the corrects DSUM calculation in the immediate window, using ?MyInvVarNeg(#1/9/2014#)
---
Public Function MyInvVarNeg(CurrentDay As Date)
MyInvVarNeg = Int(Nz(DSum("[RecCount]", "qInvIndNegVar", "[RecDate]>#" & CurrentDay & "#-.001 and [SenderID]=CurrentUser()"), 0))
End Function
---
When I change the CurrentUser() function that's in the DSUM into a string variable, I get a DSUM of zero, which is incorrect, even though I declare the variable as the CurrentUser() function in the immediate window, ?MyInvVarNeg(CurrentUser(), #1/9/2013#)
---
Public Function MyInvVarNeg(CurrentUseID as String, CurrentDay As Date)
MyInvVarNeg = Int(Nz(DSum("[RecCount]", "qInvIndNegVar", "[RecDate]>#" & CurrentDay & "#-.001 and [SenderID]=CurrentUseID"), 0))
End Function
---
The [SenderID] field that I'm attempting to match is of the Text data type in the table that is queried, and is generated by another function and defined as a string in that function.
What am I missing? Why does CurrentUser() work in this function when hardcoded, but I can’t seem to get it to work as a variable?