Jan 14 2022 05:11 PM
Jan 14 2022 05:11 PM
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 variable in a function.
Can this be done?
Jan 14 2022 06:48 PMSolution
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)
getQueryParam = varParam
your Global/Public variable is varParam.
Jan 14 2022 07:09 PM
Jan 15 2022 01:05 AM
Jan 15 2022 06:38 AM
Jan 15 2022 10:19 AM
Jan 15 2022 11:26 AM - edited Jan 15 2022 11:29 AM
Perhaps the demo I previously linked to MIGHT be useful after all. Especially so because it does illustrate the helper functions used in the Iif() function in the queries.
Jan 15 2022 04:42 PM
Jan 15 2022 07:42 PM
Jan 15 2022 08:08 PM
Jan 16 2022 05:43 AM - edited Jan 16 2022 09:56 AM
First, I think I owe you an apology because I glossed over the idea of wanting to assign a value to a variable FROM the input box which opens because it is in the parameter of the query. That's not possible, but in my narrow focus, I didn't give it adequate attention.
Variables are defined and used in VBA. SQL in Access does have the ability to invoke and to use VBA functions and global variables, but SQL can't set or create variables. So, you'll need to create the variable--or tempvar--in VBA first, then you can use it in the query.
Jan 16 2022 11:04 AM
Jan 17 2022 05:41 AM
Jan 17 2022 06:15 PM
Jan 18 2022 06:24 AM
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.
Jan 18 2022 03:29 PM