Variables in Queries

[Chevy757IT]

I have a query that counts how man records match a certain criteria and what I want to do is have the query request a user to input a number value (how many people are at the company, which changes week by week) and then calculate that number to get a percentage. I have the count part but I cant figure out how to get query to request user input to input the number from the company into the equation. Is this even possible if not is there another way with forms that this can be done?

Something like this:

Expr1: [TotalEnrollmentsCountQRY]![CountOfLast Name, First Name]/[Request user input for a number]



User inputs 347

Expr1: [TotalEnrollmentsCountQRY]![CountOfLast Name, First Name]/[347]

[Bob Fitz]

Why not have a form, bound to your "count" query, with an unbound textbox for the user to enter 347. Then do the calculation in an unbound textbox on the form.

[Welshgasman]

If you do not want a form, consider InputBox and a TempVar ?

[Bob Fitz]

If you do not want a form, consider InputBox and a TempVar ?

How would you assign a value to the TempVar?

[Welshgasman]

Something along the lines of.....

tempvars("HowMany")=inputbox("How many")
debug.Print tempvars("HowMany")
12

I entered 12 to the inputbox prompt

[Bob Fitz]

Something along the lines of.....

tempvars("HowMany")=inputbox("How many")
debug.Print tempvars("HowMany")
12

I entered 12 to the inputbox prompt

Sorry, I don't think my question was very clear. What I meant was how do you run that code from a query?

[Welshgasman]

Sorry, I don't think my question was very clear. What I meant was how do you run that code from a query?

You wouldn't Bob, the same as you would not from the form.?

It is just another method to get the input, without having to create a form.?
You'd refer to the TempVar in the same way that you would the Form control in the query, just that this method is a little simpler in my mind.?

[Micron]

Not that I think users should be working with queries but what's wrong with a simple parameter prompt for this? If [Request user input for a number] is not the actual name of a field then I'm missing something because that ought to do exactly what's being asked for - as long as it's understood that the value can only be entered once for all records.

[Welshgasman]

Not that I think users should be working with queries but what's wrong with a simple parameter prompt for this? If [Request user input for a number] is not the actual name of a field then I'm missing something because that ought to do exactly what's being asked for - as long as it's understood that the value can only be entered once for all records.

Like this?

SELECT TestTransactions.TransactionDate, TestTransactions.Description, TestTransactions.Amount, [Numberof] AS Expr1, [amount]/[expr1] AS Expr2
FROM TestTransactions;

I entered 10 for NumberOf

[Micron]

I had in mind exactly what was posted. [Request user input for a number] strikes me as not being an actual field name so just using that would cause a prompt without giving it to an alias field first. I think that what was posted was never tried - or that is a field name after all, in which case your way should work. Going to wait for OP now.

[Chevy757IT]

The [Request user input for a number] was just my way of explaining what I needed not an actual field. The number requested by the user does not need to be stored so I thin the TempVar might be the best option. My ultimate goal is have this percentage displayed on a report.

[Welshgasman]

No need for a Tempvar now, you can do as Micron suggested and I tested. ?

[Chevy757IT]

How would I do the parameter prompt?

[Welshgasman]

Just as I have in that sql?
Just replace the fieldnames for your own fields.

Here is the GUI window for that sql

[Micron]

I guess I wasn't clear enough. Trying again: just use [Request user input for a number] as a field. Doesn't matter if it's part of an equation or what. Access will not recognize it as a field so you'll get a prompt. Are you sure that [CountOfLast Name, First Name] shouldn't be [CountOfLastName], [First Name]