?
Solved

Dsum help

Posted on 2016-08-16
9
Medium Priority
?
53 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
[X]
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
  • 4
  • 4
9 Comments
 
LVL 53

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 51

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 51

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
 

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

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

/gustav
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

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