Expression Builder Syntax Problem

[maxmaggot]

Hi I have an expression duilder syntax problem.



I'm trying to calculate the total amount spent on an event in a subform based on the main form record. Here is the code I have written:

=Nz(DSum(Forms![Donations subform]![AmountPaid],[PaymentsReceived],"[PaymentsReceived]![DonerRegID]=" & Nz([Donation ID] and Forms![Donations subform]![DonationType] = "Fund Raising Event",0)),0

It's probably simple but your help is much appreciated.

[June7]

Can't use domain aggregate functions (DSum, DAvg, etc) with references to form and form controls as the data source. Domain aggregate functions must reference tables/queries and fields as data source. Maybe you want:

=Nz(DSum("AmountPaid", "PaymentsReceived", "DonerRegID=" & [DonationID] & " AND DonationType='FundRaisingEvent'")

Use query aggregate functions (Sum, Avg, etc) in queries and on forms/reports to do aggregate calcs with the query or form/report RecordSource as data source. Set the subform as Continuous and in textbox in form footer: =Sum([AmountPaid])

However, aggregate calcs are best done in reports.

If you want to provide db for analysis, follow instructions at bottom of my post.

[maxmaggot]

Thanks so much. It says one parethisis missing. Can't spot it. Any idea? Thanks again. Cleared up alot of confusion for me.

[maxmaggot]

Found it. thanks so much.

[maxmaggot]

Can I PM you the db to look at the expression on the Funds page at the left. It contains hospital records for our leukaemia charity so I don't want it public

[June7]

You can upload the db to a fileshare site such as Box.com then pm me the link.

Or make copy and remove confidential data and attach to post.

Don't I already have the db from other thread? Exactly which form is involved in the issue?

Don't know if you are going to make deadline. I'm about to go to bed.

[June7]

The PaymentsReceived table is actually saving the EventType code, not the description (because of combobox lookup). So the criteria must be the code not the description.

=Nz(DSum("AmountPaid","PaymentsReceived","DonerReg ID=" & [Donation ID] & " AND EventType=2"))

That gets rid of the error in textbox but no data shows. Okay, think I found source of issue. The Lookups for EventType and DonationType in PaymentsReceived appear to be swapped. Instead of saving the EventType code in EventType field, it is saving the DonationType code. EventType code is saved in the DonationType field.

An odd thing is I can find only one record for DonerID 294 in PaymentsReceived even though the form shows 2.

Also, change expressions for Individual calcs:

=Nz(DSum("[TotalPrice]","[ChristmasCards]","[DonerRegID]=" & Nz([Donation ID],0)),0)

=Nz(DSum("[NumOrdered]","[ChristmasCards]","[DonerRegID]=" & Nz([Donation ID],0)),0)

[maxmaggot]

Can't thank you enough for your work. Must have driven you mad too

Thanks so much
Kind Regards
Karl