Assigning Criteria Result to a Variable

[MaxCA1000]

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?

[#2 · Accepted solution]

@MaxCA1000 

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 If

getQueryParam = varParam

end function

your Global/Public variable is varParam.

[#3]

Thank you for your suggestion. It's clever and I think it might work. I'll try it this weekend and let you know.

[#4]

You could also consider using a tempvar. These can be used directly in a query.
No need for a user defined function

[#5]

i just tested with Tempvars as Criteria, the query runs without any prompt for a Criteria.

[#6]

@arnel_gp 

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.

[#7]

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)

[#8]

This doesn't produce an error, it just doesn't function.

[#9]

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.

[#10]

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.

[#11]

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.

[#12]

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.

[#13]

@MaxCA1000 

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. 

[#14]

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.

[#15]

@MaxCA1000 

you can't use same parameter on different field, specially when the fields have different data type.

[#16]

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.

[#17]

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.

[#18]

You can - please see my reply to George Hepworth.

[#19]

@MaxCA1000 

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.

[#20]

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.