Solved

Dsum and Calculated Field / Parent & Sub

Posted on 2016-09-20
8
25 Views
Last Modified: 2016-09-20
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
Comment
Question by:pdvsa
  • 5
  • 2
8 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
Comment Utility
Try this ...

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

ET
0
 

Author Comment

by:pdvsa
Comment Utility
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
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 500 total points
Comment Utility
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

by:Eric Sherman
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
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
 

Author Closing Comment

by:pdvsa
Comment Utility
thats what it was.  thank you.  The where clause was not exactly correct. Grateful for your help...
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
Based on your picture ... try this ...

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

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
Glad to help ... Thanks for the points.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now