Solved

Dsum and Calculated Field / Parent & Sub

Posted on 2016-09-20
8
27 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
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 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.

ET
0
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 13

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

943 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

4 Experts available now in Live!

Get 1:1 Help Now