Search code examples
sqlms-accessms-access-2016

Average of a query column put into a Text Box

I want to put an average of my query's column into a text box. The intent is to find the average of the marks a student has and put it into a text box in the form. The code I've put in the Control Source field so far looks like this:

= Avg(SELECT Marks.Mark
FROM Marks
WHERE ([IdS]=[Marks].[IdS]))

Where [IdS] is a text box with student ID of the selected student in this form. When I go back to the form view the text box gives me #Error.

Any idea?

Solution

  • You are using a SQL statement for a control record source, or you need to use the query as the underlying form source or use a domain aggregate to get the desired result=DAvg("[Marks]![Mark]","Marks"," [Marks]![ID] = [IdS] "). (Don't forget to set a form requery when changing the [IdS] value)