Form With Two Comboboxes - DAvg (1 Viewer)

Lilfiger19

New member
Local time
Today, 07:54
Joined
Mar 16, 2015
Messages
6
Hi,

Can anyone tell me what I am doing wrong with the below SQL statement? I am writing this so that when a selection is chosen from each of the combo boxes (Name and Month/Year) then it will look back at a table and perform an average on a specific field. To achieve this, right now, I have this under the After Change Event.

With the code below, I keep getting the error message.

"The expression you entered contains invalid syntax, or you need to enclose your text data in quotes."

Code:
=IIF (((tblFIRStats.[Associate])="[cbxAssociate]"), 
        (Select
            AVG(tblFIRStats.[FIR_Perc])
            From tblFIRStats
            Where ((tblFIRStats.[Associate])="[cboAssociate]"))),
       (Select
            AVG(tblFIRStats.[FIR_Perc])
            From tblFIRStats
            Where ((tblFIRStats.[Month])="[cboMonYr]")))

Please, if anyone can help me I will be greatly appreciative.
 

shoji

Registered User.
Local time
Today, 12:54
Joined
Feb 28, 2015
Messages
83
Yes, many things are wrong.

1. You cannot refer to a value of a table as you do.
2. "[cbxAssociate]" does not refer to the value of the combo box. It refers literally to the string "[cbxAssociate]".
3. You cannot just write a SQL statement inside IIF function. A SQL statement is a string, so it must be enclosed with quotation marks.
4. The first SQL does not include any date qualifier? And the second one not the Associate qualifier?

When you want to use a SQL statement, create a query and test it first.

Having said that, my guess of what you want is something like

Code:
= DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & _
           "' AND [Month] = '" & Nz([cboMonYr]) & "'")

Here I am assuming cboMonYr is a text. If it is a number, the second line should be

"' AND [Month] = " & [cboMonYr])
 

Users who are viewing this thread

Top Bottom