Solved

Dsum help

Posted on 2016-08-16
9
52 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 52

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 50

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 50

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 50

Expert Comment

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

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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.

707 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