Solved

Dsum help

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

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
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…

827 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