Forum Discussion
MaxCA1000
Jan 15, 2022Copper Contributor
Assigning Criteria Result to a Variable
In my query, I have a criteria requiring user input. I want to be able to assign that user input to a public variable and pass it to an iif expression in the same query, and also be able to call the ...
- Jan 15, 2022
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.
MaxCA1000
Jan 15, 2022Copper Contributor
Thank you for your suggestion. It's clever and I think it might work. I'll try it this weekend and let you know.
isladogs
Jan 15, 2022MVP
You could also consider using a tempvar. These can be used directly in a query.
No need for a user defined function
No need for a user defined function
- arnel_gpJan 15, 2022Steel Contributori just tested with Tempvars as Criteria, the query runs without any prompt for a Criteria.
- George_HepworthJan 15, 2022Silver Contributor
There is a sample download on my website in which I demonstrate using tempvars as parameters in queries, along with some other uses. I ended up wrapping the tempvars in helper functions, however, for reasons I've forgotten at the moment.
- MaxCA1000Jan 15, 2022Copper ContributorThanks for the input.
I think I can create the TempVar:
[TempVars,"MYVar",InputBox("Enter Month Number")]
but I can't pass it to the next field in the query:
IIf([TempVars]![MYVAR]=12,2021,2022)