asked on # Dsum help

Experts,

Im tring to sum the amount in the form in the attached db. I am way off and have spent too much time trying to see where I am wrong so I am turning it over to the experts. I have pared down the db to only what is needed and the form appears auto. Please double click the amount in the form that appears on auto and then another form appears that shows the amount calculated with the formula below. I am just trying to sum these amounts. I imagine an expert has a different way of doing this. I am not sure if dsum is the correct method.

=Sum(Format(Nz(DSum("[Amount]","tblDraws_Details1","[ID] = " & [tblRepayment].[DrawIDrpmt]),0),"Standard"))+

+Sum((Format(Nz(DSum("[Amount]","tblRepayment","[DrawIDrpmt] = " & [tblRepayment].[DrawIDrpmt]),0),"Standard")))

the answer should be

-6,796,284.00 (282,000,000.00-282,000,000.00-6,010,597.00-785,687.25)

but I am getting:

-20,388,852.75

I am greatful for your kindness and time.

thank you. please see attached.

EE.accdb

Im tring to sum the amount in the form in the attached db. I am way off and have spent too much time trying to see where I am wrong so I am turning it over to the experts. I have pared down the db to only what is needed and the form appears auto. Please double click the amount in the form that appears on auto and then another form appears that shows the amount calculated with the formula below. I am just trying to sum these amounts. I imagine an expert has a different way of doing this. I am not sure if dsum is the correct method.

=Sum(Format(Nz(DSum("[Amou

+Sum((Format(Nz(DSum("[Amo

the answer should be

-6,796,284.00 (282,000,000.00-282,000,00

but I am getting:

-20,388,852.75

I am greatful for your kindness and time.

thank you. please see attached.

EE.accdb

Microsoft Access

Hi Pat,

thanks for the response.

Would you happen to know why I get a #error?

thanks for the response.

Would you happen to know why I get a #error?

There is something wrong with the expression.

Where in the app is the expression you posted? If I get a minute, I'll try to fix it.

Where in the app is the expression you posted? If I get a minute, I'll try to fix it.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Nice!

that works. I will need to ask a follow up because I need another condition of tblRepayment.PaymentMadeYN="Yes". I am fiddling with it and cant figure it out. thank you for your expert assistance and the tips about dsum and nz. I am not a coder as you probably figured out.

that works. I will need to ask a follow up because I need another condition of tblRepayment.PaymentMadeYN

Pat, will post another question.

=Sum(DSum("[Amount]","tblD

+Sum(DSum("[Amount]","tblR

I don't know where you want this on the form so I didn't try it.

You can set the format of the control to standard rather than embedding the Format() function in the expression.