• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

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
0
Petrobras
Asked:
Petrobras
  • 9
  • 6
1 Solution
 
dirknibleckCommented:
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)


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
0
 
dirknibleckCommented:
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
0
 
dirknibleckCommented:
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?


0
 
PetrobrasAuthor Commented:
that worked perfectly!  thank you!
0
 
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)
0
 
dirknibleckCommented:
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.

Try:

nz(DSum("[Amount]","tblLetterOfCredit","[facilityDetailsID] = " & [facilityDetailsID] & " AND [ExpiredYN] = 'Yes'"),0)+nz(DSum("[AmountDrawn]","tblDrawDown","[facilityDetailsID] = " & [facilityDetailsID] & " [RepaidYN] = 'Yes'"),0)
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
0
 
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)
0
 
dirknibleckCommented:
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)
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
0
 
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
0
 
dirknibleckCommented:
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)
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.
pb
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now