DSum condition

Experts, I have the following DSum in the control source of [Usage] on frmFacilityDetails:
=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID]),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID]),0)

and it works great.  It sums many records on tblLetterOfCredit and tblDrawDown and returns a total amount of usage.  I now need to now modify it based on a cbo.  I have a cbo with a "Yes" or "No" on each of the tables and when the particular amount has been repaid then a user selects "Yes" and the amount should be excluded from the DSum.  The form will not be open that has the cbo.  

If you need more information let me know.   I am thinking that their needs to be code placed on "On Current".  

thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If the cbo is recorded to the table, then you probably just need to add this to the WHERE part of the Dsum.

Ie: =nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND [cbo] <> 'Yes'"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " AND [cbo] <> 'Yes'"),0)

PetrobrasAuthor Commented:
dirk, I tried it out and I think it has to be
AND [cbo]  to ==> AND minus the [cbo]  
How do I subtract it?  It was returning the opposite of what I wanted at least it seems.  

thannk you
What is the value that is being written to the table for cbo? Are you writing 'Yes'?
Or do you want to subtract the DSUM of the cbo?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PetrobrasAuthor Commented:
dirk, my cbo is a value list with "Yes" or "No".  It is not a YN field.  To answer your question, I think tha teither Yes or No would be written to the table.  I do want to subtract the DSum if it is "Yes".

thank you
If it is writing to the db as "Yes" or "No", my earlier code should work,

But alternatively you could use your original code with a - DSum such as:

=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID]),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID]),0)  - nz(DSum("[Amount]","tblLetterOfCredit","[cbo] = 'Yes'"),0)-nz(DSum("[AmountDrawn]","tblDrawDown","[cbo] = 'Yes'"),0)

But since we're testing for the value of cbo, we really need to know what it is recording. When you look at the table, can you see what values are written for cbo? Is it yes, Yes or YES?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PetrobrasAuthor Commented:
that worked perfectly!  thank you!
PetrobrasAuthor Commented:
Hi Dirk, not sure if you are still montoring.  If so, I have to ask a question about the DSum.  
I need to ask a question about the criteria after the MINUS sign:  Is it per FacilityDetailsID like the first 2 parts?  I am thinking that it is not but not sure.  After testing more, I am not getting an incorrect DSum and I cant track it down as to why.   I hope that it can be modified for criteria FAcilityDetailsID for each tblLetterOfCredit and tblDrawDown.
thanks in advance
=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID]),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID]),0)-nz(DSum("[Amount]","tblLetterOfCredit","[ExpiredYN] = 'Yes'"),0)-nz(DSum("[AmountDrawn]","tblDrawDown","[RepaidYN] = 'Yes'"),0)
Oh yes, I guess you would need the facilityDetailsID. But in that case, you're still going to need to do an AND, in which case you probably will want to ignore the minus completely.


nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND [ExpiredYN] = 'Yes'"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " [RepaidYN] = 'Yes'"),0)
PetrobrasAuthor Commented:
Hi DIRK:  I would need for it to MINUS the ExpiredYN and RepaidYN because these amounts have been repaid and need to be subtracted from the DSUM.  

thank you
PetrobrasAuthor Commented:
I do get  a #error though...not sure why.  The names appear to be correct.  Copy and paste from above
=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND [ExpiredYN] = 'Yes'"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " [RepaidYN] = 'Yes'"),0)
What is the error?

The reason I suggest ignoring the minus is because we're not going to add up records where the cbo equals Yes. But I do notice that I gave you bad code for that, I forgot to change the = to <>, and we need an AND in the second part of the statement - this is probably the cause of the error.

=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND [ExpiredYN] <> 'Yes'"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " AND [RepaidYN] <> 'Yes'"),0)
PetrobrasAuthor Commented:
Dirk, I see we are excluding records that have a "Yes" now.  I will test later as I do not have access on this computer.  

thank you
PetrobrasAuthor Commented:
Hello Dirk, (I suppose that is your name??)  I tested and it does work HOWEVER I think it has to be tweaked.  What I mean is that if either the [ExpiredYN] or the [RepaidYN] is NULL meaning that neither a YES nor NO is selected the DSum will not see ithe amount.  

So, lets say that the facility is $2 and a $1 usage has neither a Yes or NO on either the Y/N fields then the DSUM would say $2 when in fact there is $1 usage.  So, would need something to handle that null.   I thought that the code actually did because it has nz in it.

thank you
Try putting the nz formula around the cbo columns or you could add OR IS NULL:

=nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND ([ExpiredYN] <> 'Yes' OR [ExpiredYN] IS NULL)"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " AND ([RepaidYN] <> 'Yes' OR [RepaidYN] IS NULL)"),0)
PetrobrasAuthor Commented:
Dirk, that did it.  Thank you for coming back to this question. I appreciate your efforts.  

thanks again...have a good one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.