Aggregate query used as ControlSource of text box

[GraeagleBill]

Somewhat continuing from a previous thread regarding the use of the SUM aggregate function. While it functions okay from the design mode of a query, attempting to use the aggregate query as the ControlSource of an unbound text box has a problem I can't find. Note in the image below that the "Continuous" form "Portfolio" has as its RecordSource a query that includes ALL fields of the Portfolio table.





Is this really the best approach?

[pbaldy]

You can't have SQL in the control source. Nearest equivalent would be a DSum() using the full form reference.

DLookup Usage Samples

[GraeagleBill]

Trying to follow Baldy's suggestion, I'm still getting more-or-less the same errors when I attempt to use DSUM.

[pbaldy]

Check the link again; concatenate the form value, and I'd use the full reference.

[GraeagleBill]

Sorry Paul, you've lost me. How do the DLookup samples at the link you sent relate to the use of the DSUM function?

[pbaldy]

From the link:

Note that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)

That includes DSum().

[GraeagleBill]

=DSum("[Shares]","Ledgers","[InvstID] = " & forms!Portfolio!InvstID)

YEAH!!!

Thanks,
Bill

[pbaldy]

Happy to help Bill!