Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dsum and Calculated Field / Parent & Sub

Posted on 2016-09-20
8
Medium Priority
?
39 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 2000 total points
ID: 41807428
Try this ...

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

ET
0
 

Author Comment

by:pdvsa
ID: 41807458
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 2000 total points
ID: 41807474
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41807503
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 19

Expert Comment

by:John Tsioumpris
ID: 41807506
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
ID: 41807507
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
ID: 41807514
Based on your picture ... try this ...

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

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41807517
Glad to help ... Thanks for the points.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

916 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