Totals on Subforms

[buckeye77]

There are two subforms: "Advances" and "Reimbursements" on each form there is a field named "RequestedAmt" as well as a field named "CalandarYear". What I need to do is have a field on the main form add all the amounts from the "RequestedAmt" fields from "Advances" and "Reimbursements" where "CalandarYear" = 2008. This is to give me a total of 2008. I have already tried having each form calculat this total in a field named "2008" and thought I could simply add this field from the subforms together and show the total on the main form but I have had no luck. Any help is greatly appreciated as this is geating rather disturbing. Thank you in advance for any help.

 

[genomon]

Can you use the DSum() function with the optional WHERE clause text to choose the data you need from a source table?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[buckeye77]

Honestly, I dont know what I can use.... will will try but not sure if I know how to use the "WHERE" clause let alone how to use the DSUM() to begin with, but I'll try. Thanks and I will post to let you know how it goes or how it does not go.

 

[genomon]

It's in Access help - super easy.
DSum("[fieldname]","tablename", "CalandarYear = 2008")

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[buckeye77]

Tried:
=DSum([Advances].Form!RequestedAmt,[«domain»],[Advances].Form!CalandarYear=2008)

That came with the error #Name?

Then took our the [<<domain>>] and the error #Error showed up....

Any suggestions as to where I went wrong?

 

[genomon]

First of all "domain" (the second parameter) is the name of the table or query, not the spelled out word "domain". The first parameter is the name of the field from the table or query that you want to sum, not a form control name. AWnd the third parameter is the filter without the word WHERE, enclosed in double quotes for instance, "[CalendarYear] = 2008". Please take another look at Access help, as it is quite explicit about this. What class is this for?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[buckeye77]

Not a class... this is a database I am working on for work. Driving me nuts as I am trying to merge accounting and HR files into one database.

 

[buckeye77]

used the following example from access help as a template:
DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
and made the following:
DSum("[RequestedAmt]","Advances","[CalandarYear]='2008'")

however the error "#Error" appears.

 

[buckeye77]

Got it! took out the ' ' around 2008 as per a previous suggestion and all works... so far... thanks!!!!

 

[buckeye77]

Small issue found.... When I use the DSUM, regardless the record showing, the field 2008 (which is the field that has the DSUM calculation) shows the total for all records that meet that criteria.
I.E. Record one: 200 in CalandarYear 2008
Record two: 3000 in CalandarYear 2008

However on record one the field 2008 shows 3200 and 3200 also appears on record two. Is there a way to only add current record totals or could I have an issue with how the forms / subforms are set up?