Forum Discussion
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 ...
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.
In the Help for Input Boxes, Microsoft says that an input box returns a string based on the user's input, but it says nothing about how to retrieve that string.
The SQL for a parameter query uses the text on the criteria line to identify the parameter. I suspect the input box creates a string with that name and passes it to the SQL. This works:
SELECT tblTransactions.CheckNumber, tblTransactions.Amount
FROM tblTransactions
WHERE (((tblTransactions.CheckNumber)=[Enter #]) AND ((tblTransactions.Amount)=[Enter #]));
The same string persists as the second parameter. That's a start on what I'm trying to do.
The SQL for a parameter query uses the text on the criteria line to identify the parameter. I suspect the input box creates a string with that name and passes it to the SQL. This works:
SELECT tblTransactions.CheckNumber, tblTransactions.Amount
FROM tblTransactions
WHERE (((tblTransactions.CheckNumber)=[Enter #]) AND ((tblTransactions.Amount)=[Enter #]));
The same string persists as the second parameter. That's a start on what I'm trying to do.
Please read Arnel's post and re-read my previous post.
The reason Microsoft documentation says nothing about "how" to retrieve that string is that it can't be done that way.
You can't do what you are trying to do with an input box generated by a parameter in a query. [Enter#] supplies ONE string for that ONE parameter in SQL. It isn't available to VBA to set it as a global variable or a tempvar.
You CAN write a function in VBA to retrieve that value from an input box in the VBA function assign it to a global variable or tempvar. That variable or tempvar--once set in VBA--is available for other parameters.
In other words, you can go from VBA to a SQL statement, but not the reverse.
The reason Microsoft documentation says nothing about "how" to retrieve that string is that it can't be done that way.
You can't do what you are trying to do with an input box generated by a parameter in a query. [Enter#] supplies ONE string for that ONE parameter in SQL. It isn't available to VBA to set it as a global variable or a tempvar.
You CAN write a function in VBA to retrieve that value from an input box in the VBA function assign it to a global variable or tempvar. That variable or tempvar--once set in VBA--is available for other parameters.
In other words, you can go from VBA to a SQL statement, but not the reverse.
- This now fully works without any hard coding of the year. The user now has a single input box, to specify the month. If the month is 12, the query selects transaction in the previous year; if not 12, it selects for the current year:
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. 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.
- I don't think you've paid enough attention to my question. All I want to do is access the data entered by the user in the input box and apply it as a query parameter in the same query - I don't specifically want a global variable or a tempvar except as a possible means to the end I want to achieve as I don't need the input data to persist.
It is possible to retrieve the value from an input box and use it to parameterize another field in the same` query. This does just that:
SELECT tblTransactions.CCMOnth, tblTransactions.CCYear, tblTransactions.Payee, tblTransactions.Amount, tblTransactions.Categories
FROM tblTransactions
WHERE (((tblTransactions.CCMOnth)=[Enter M]) AND ((tblTransactions.CCYear)=IIf([Enter M]=12,2021,2022)) AND ((tblTransactions.Categories)="Credit Cards"));
In this case, the input against the CCMonth parameter creates the input for the CCYear parameter. But it's not a full solution because the years are hard-coded into the expression builder
As written now, the query calls a single input box, which is what I wanted to achieve. But hard-coding the year is very inelegant and what I want to do is use the calculated variable ExpYear to extract the year:
ExpYear: DatePart("yyyy",Now())
I don't know why Access allows me to use one variable, [Enter M], and not a second one, [ExpYear]. I can't find any documentation on this.
Incidentally, I can use Cint to convert [Enter M] to an integer and perform calculations on it.
