I need some help with designing a query.
The attached (testing) database contains the following:
Process upon opening the database:
1. Open form "frmLogin"
2. Select any value from the listbox (this will open 'rptGeneric')
3. Then, open query 'Query1'
- Query1 displays the average value for the 3 fields (Table 1)
- The 4th field "FormListBoxValue" is an expression which displays the last selected value from "frmLogin"
Here's what I need help with:
- Use the expression value (e.g., "AVG_3_Digit") as a baseline for another SQL expression so that I can compute the AVG value of any of the 3 fields based on whatever was selected.
For example (pseudo code) for the 2nd expression. For example, change SQL from/to:
From: "SELECT Avg(Table1.[2-DigitNumber]
) AS AVG_2_Digit FROM Table1;"
To: "SELECT Avg(Table1.[Forms]![frmLog
oxTest]) AS AVG_2_Digit FROM Table1;"
Question #1: The proposed (pseudo) code -- with the [Forms statement] -- does NOT work. How can I utilize a selected value from a form as field input for a SQL query?
Question #2: Right now, the form's listbox values mimic the query expressions (e.g., "AVG_2_Digit"). In the actual database, I need to be more descriptive with options in the form's listbox. For example, the listbox may include options such as "Run report with average of 2-digit numbers." That said, how can I translate that listbox value to match up with the actual field name [2-DigitNumber] or [AVG_2_Digit]?
Well, first things first... if I can get help with question #1, that would be a great starting point.