Dsum and Calculated Field / Parent & Sub

Experts,

I seem to get a #error in the below Dsum.  
[USD Equiv] is a calculated field in a table and I guess this is why I get the error as the formula works fine when I switch [USD Equiv] to [Amount] (Amount is not a calculated field in the same table)

here is the formula that returns #error (this is in a text box in the Parent form):
=Nz(DSum("USD Equiv","tblDraws_Details1","DrawID1=" & [ID]),0)

here is the calculated field [USD Equiv] in the subform:
USD Equiv: IIf([CurrencyID]=1,[Amount],[Amount]*[ExchangeRate])

How can I sum [USD Equiv].[tblDraws_Details1] (The Subform) and show this sum in the Parent?

Form Names:
subform:  subfrmlDraws_Details1
parent:  frmDraws

thank you
pdvsaProject financeAsked:
Who is Participating?
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Try this ...

=Nz(DSum("[USD Equiv]","tblDraws_Details1","DrawID1=" & [ID]),0)

ET
0
 
pdvsaProject financeAuthor Commented:
HI ET, thanks.  I see you put the brackets in.  Darn. So many ways I can get it wrong.

it seems to still not sum though for all the records.  I cant figure out that if there is > I record in the subform, it wont sum.  It will sum if 1 record only.

i am not sure if Dsum is the correct function to use to sum all records?

You can see below, it sums (only 1 record)
SumsIt


here you can see it doesnt sum:
NoSumUSDEquiv
0
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Sounds like your WHERE Clause is the issue .... "DrawID1=" & [ID]

Is [ID] the unique autonumber field??  If so, you have only 1 unique record.

ET
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Eric ShermanAccountant/DeveloperCommented:
Can you load up a small sample db???  I'm not quite sure how you have things structured ... would need to see it to come up with a good solution.

ET
0
 
John TsioumprisSoftware & Systems EngineerCommented:
i think you want a running sum
Nz(DSum("[USD Equiv]","tblDraws_Details1","DrawID1=" <= [ID]),0)

Open in new window

In order for this to work your ID should be ordered ascending
0
 
pdvsaProject financeAuthor Commented:
thats what it was.  thank you.  The where clause was not exactly correct. Grateful for your help...
0
 
Eric ShermanAccountant/DeveloperCommented:
Based on your picture ... try this ...

=Nz(DSum("[USD Equiv]","tblDraws_Details1","[Currency]=" & [Currency]),0)

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Glad to help ... Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.