Forum Discussion
Assigning Criteria Result to a Variable
you can try creating a Public function for the Parameter Criteria of your query.
let say your table has a FirstName field and you want to filter it:
global varParam As Variant
public function getQueryParam()
gstrParam = ""
gstrParam = Inputbox("Input the name of person", , varParam)
If IsNumeric(varParam) Then
varParam = Val(varParam)
End IfgetQueryParam = varParam
end function
your Global/Public variable is varParam.
I see. The answer stands.
" I don't specifically want a global variable or a tempvar except as a possible means to the end I want to achieve ..."
You can't create a variable in SQL. You CAN create as many as you want in VBA and invoke them in SQL.
What you ARE doing now is supplying a parameter to the SQL at run time. If the same parameter is used twice, Access can do that. But if you want a second parameter, you'll have to ask for it the same way.
You have three choices.
- Use the current approach and add a second Runtime parameter request for years.
- Create variables or tempvars in VBA and use those as criteria in the SQL.
- Put controls on a form in which you enter the parameters and reference those controls as criteria in the SQL.
SELECT tblTransactions.CCMOnth, IIf([Enter M]=12,DatePart("yyyy",Now())-1,DatePart("yyyy",Now())) AS ExpYear, tblTransactions.Payee, tblTransactions.Amount, tblTransactions.Categories
FROM tblTransactions
WHERE (((tblTransactions.CCMOnth)=[Enter M]) AND ((tblTransactions.Categories)="Credit Cards"));
Thank you for your input. It has been interesting learning about tempvars and I'm sure I'll use them in the future.