Solved

# Dsum and Calculated Field / Parent & Sub

Posted on 2016-09-20
25 Views
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
0
Question by:pdvsa
• 5
• 2

LVL 19

Accepted Solution

Eric Sherman earned 500 total points
Try this ...

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

ET
0

Author Comment

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)

here you can see it doesnt sum:
0

LVL 19

Assisted Solution

Eric Sherman earned 500 total points
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

LVL 19

Expert Comment

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

LVL 13

Expert Comment

i think you want a running sum
``````Nz(DSum("[USD Equiv]","tblDraws_Details1","DrawID1=" <= [ID]),0)
``````
In order for this to work your ID should be ordered ascending
0

Author Closing Comment

thats what it was.  thank you.  The where clause was not exactly correct. Grateful for your help...
0

LVL 19

Expert Comment

Based on your picture ... try this ...

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

ET
0

LVL 19

Expert Comment

Glad to help ... Thanks for the points.
0