Solved

Dsum help

Posted on 2016-08-16
9
40 Views
Last Modified: 2016-08-18
Experts, how can I modify the below to sum only for the following condition:

tblRepayments.PaymentMadeYN = "Yes"
the YN field has a row source of "Yes";"No";"" (its a combo box)
I need to sum only for when a payment was made.

=DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt])+DSum("Amount","qryBal","DrawIDrpmt = " & [DrawIDrpmt])

thank you.
0
Comment
Question by:pdvsa
  • 4
  • 4
9 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41758887
what's the relevant field in tblDraws_Details1 and qryBal for PaymentMadeYN respectively?

if the field name is PaymentMadeYN, then you probably can customize like:

=DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = " & [yourComboBox])+DSum("Amount","qryBal","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = " & [yourComboBox])

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41759005
I guess you will need quotes:
=DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [yourComboBox] & "'")+DSum("Amount","qryBal","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [yourComboBox] & "'")

Open in new window

However, if PaymentMadeYN is a Boolean - which it should be - you would use:
=DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN =  " & IIf([yourComboBox] = "Yes", True, False) & "")+DSum("Amount","qryBal","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN =  " & IIf([yourComboBox] = "Yes", True, False) & "")

Open in new window

/gustav
0
 

Author Comment

by:pdvsa
ID: 41759983
Well I have spent quite a few hours on this and i dont know why I cant get it right.  Maybe one of you guys can see what the issue is.  to recap, I am trying to get a balance on the form that appears after you double click the amount on the auto open form.   The condition is what is difficult:  ONly sum for PaymentmadeYN = "Yes".  

Maybe someone can take apeek when they have time.  Please see pared down db.  I have named each formula as Gustav and Ryan.  Btw:  same answers for both.
EERyanGustav.accdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41760156
I think you need to pinpoint where/when the error values are, and what they are expected to be.

Have in mind please, that we have only little idea of what your application is supposed to provide.

/gustav
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.

 

Author Comment

by:pdvsa
ID: 41760468
Gustav, apologies.  I will explain what I am looking for after the double click on the main form's value.

The first record that appears on the auto execute form is a value of 282,000,000.  After double clicking this amount, the next form that appears is frmRepayments (tblRepayments) and the following values appear (they are manually typed)
-282,000,000  ([tblRepayments].[paymentMadeYN] = "Yes")
-6,010,597.00 ([tblRepayments].[paymentMadeYN] = "Yes")
-785,687.25   ([tblRepayments].[paymentMadeYN] = "No")

therefore, the value I am looking for in tblRepayments is as follows:
282,000,000 (from tblDrawsdetails1) +-282,000,000 (tblRepayment)+-6,010,597 (tblRepayment) = -6,010,597
basically subtracting from tblDrawsdetails1.value ONLY the records where ([tblRepayments].[paymentMadeYN] = "Yes") from the value in tblRepayments WHERE tblDraws_details1.ID = tblRepayment.DrawIDrpmt

you can see that the way the formula is now, a value of 846,000,000.00 is returned and not the required -6,010,597 (as shown above).  I also note that it seems the formula reads correctly but the correct value is not returned.  

I do hope that clears it up a bit.  I am greatful for your expert assistance and kindness.  Please dont hesitate to query me if it is unclear.
thank you sir...
0
 

Author Comment

by:pdvsa
ID: 41760479
Gustav / Ryan:  I did make a change in your initial formula frm qrybal to tblRepayment
 (see bold underline)
=DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")+DSum("Amount","tblRepayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")

and I do get an answer of -6,010,597.00 for the first 2 records but you can see that for the record with  in the tblRepayments is where tblRepayments.PaymentmadeYN = "No" the value of -785,687.25 is returned but I want it to be the same -6,010,597 as shown in the first 2 records where  tblRepayments.PaymentmadeYN = "Yes".  Basically, the needed formula I am looking for should display the same value for all records...it is a balance for the entire record where WHERE ([tblRepayments].[paymentMadeYN] = "Yes") and even though that last record where where ([tblRepayments].[paymentMadeYN] = "No")  I still need for the formula to display the summation where ([tblRepayments].[paymentMadeYN] = "Yes")

I have  a feeling my explanation is wordy and hopefully it doesnt confuse.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41760679
OK, that helped. I believe this would do:
EEsub.PNG
Also made it to update when changing payment. See the attached demo.

/gustav
EERyanGustav.accdb
0
 

Author Closing Comment

by:pdvsa
ID: 41760747
Gustav,  Perfect!  that formula was...well lets say for the experts.  Nice.  I wish I had your knowledge.  Have a good day.  and thank you once again for your help.  I also liked the afterupdate code you did on the paymentYN field.  Very nice touch.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41760774
You are welcome! Hope all is well in SA.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
ms/access ftp / SFTP 3 32
Open a Specific Record With a Specific Tab Page 5 29
Alter an update query which rounds 7 30
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

914 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

19 Experts available now in Live!

Get 1:1 Help Now