求助：将学生成绩平均值放入表单文本框出现#Error错误

Hey there, that #Error you're seeing is because the way you're using Avg() with a subquery in the Control Source doesn't play nice with Access's syntax rules. Let's break down the fixes:

Option 1: Use the Domain Aggregate Function DAvg #

Access has domain functions specifically designed for calculating values directly in controls, macros, or VBA—DAvg is perfect for this scenario. It's simpler and more reliable here:

=DAvg("[Mark]", "Marks", "[IdS] = " & [Forms]![YourFormName]![IdS])

Quick Notes: #

• Replace [YourFormName] with the actual name of your form.
• If IdS is a text field (not numeric), wrap the control value in single quotes to avoid type mismatch:

  =DAvg("[Mark]", "Marks", "[IdS] = '" & [Forms]![YourFormName]![IdS] & "'")
  

• Double-check that the IdS field in the Marks table matches the data type of your form's IdS text box (numeric vs text)—mismatches are a common culprit for errors.

Option 2: Adjust the Subquery Syntax #

If you prefer sticking with a subquery, you need to wrap the entire subquery in an extra set of parentheses so Access recognizes it as a valid dataset for Avg():

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

That said, this method can be less stable across different Access versions, so Option 1 is usually the better bet.

Extra Troubleshooting Checks #

• Verify all names are spelled correctly: Make sure your form's text box is really named IdS, and the Marks table has fields Mark and IdS (typos are easy to miss!).
• Handle empty records: If the selected student has no scores, the average will return null. Use Nz() to show 0 or a message instead:

  =Nz(DAvg("[Mark]", "Marks", "[IdS] = " & [Forms]![YourFormName]![IdS]), 0)
  

• Ensure the IdS text box has a valid value when the form is in view mode—an empty value will trigger an error too.

内容的提问来源于stack exchange，提问作者Andrej Korowacki