Summing Top Values from a SubForm on a Main Form 1

[TCARPENTER]

I have a sub form which displays as a data sheet and in the form footer, I have an unbound text box with the sum I need, and then on my main form, I have another unbound text box with it's source set to the text box on my sub form.

Then I realized I need to sum the top 4 values - not all of them. I thought I could change my unbound text box source from =Sum([Bars]) to =Sum(Select Top 4 Bars from Scores) - but I keep getting the dreaded #Error.

How do I do this?

 

[dhookom]

As you have discovered, this won't work. You can't use a SQL statement in a control source. You can create a saved totals query that can be queried using DLookup() or DSum().

Duane
Hook'D on Access
MS Access MVP

 

[TCARPENTER]

Thanks Duane, that did it!