Dsum and Calculated Field / Parent & Sub

Posted on 2016-09-20
Last Modified: 2016-09-20

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
Question by:pdvsa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 19

Accepted Solution

Eric Sherman earned 500 total points
ID: 41807428
Try this ...

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


Author Comment

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)

here you can see it doesnt sum:
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 500 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.

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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.

LVL 17

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

Author Closing Comment

ID: 41807507
thats what it was.  thank you.  The where clause was not exactly correct. Grateful for your help...
LVL 19

Expert Comment

by:Eric Sherman
ID: 41807514
Based on your picture ... try this ...

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

LVL 19

Expert Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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