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.
Thanks 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)
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)
This doesn't produce an error, it just doesn't function.
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.
- Thank you for providing the demo but it doesn't appear to do what I'm looking to do: take a user input from a dialogue box in a query, save it as a variable and use that variable in the same query as a criteria.
- I agree. The difference is that the user input comes from controls on forms, which is a far more common strategy that asking people to type EXACTLY the correct value into an input box to get a valid match. For example, if you ask for a first name and the user types "Joohn" into your input box, the only matches will be misspelled first names in that field in the table, along with those people whose first name really IS "Joohn", if there is such a person. The problem gets more complex when you are looking at large numbers of records where variations in spelling are valid, such as "Joe" and "Joseph", or "Joan" and "Joann", etc. An input box leaves it up to the user to know, ahead of time, the exact name and spelling of that name. A combo box or list box allows users to pick from a list of names already stored in the table ensuring that misspellings don't return false results.
On the other hand, if the input box is the only method to be considered, you could, with a bit of modification, also modify the VBA in the demo to use a value from an input box rather than retrieve the value from a control on a form. I'm sure that change would be pretty straightforward. - Thank you for the detailed reply.
The user runs a parameter query to identify the month (number) and year of the recordset to be created. It is always the case that if the month is 12, then the year is the previous year; if the month is not 12, it's always the current year.
The easiest solution is to have two criteria each present a dialogue box, one for the month and one for the year, and, frankly, doing that would be no problem at all. But I want to see if it's possible to remove the year dialogue, not to resolve any real usability problem but because it would be challenging to do so and an elegant solution.
You've introduced me to TempVars, a feature of Access I'd never heard of. Now I want to explore the dialog box methods to see if I can retrieve the value of an input from the box. I'm not sure that's possible. 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.
- 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. - 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. - 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. - You can - please see my reply to George Hepworth.
