Solved

Dsum help

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

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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.

821 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