Day Count

this is a follow up to question:
http://www.experts-exchange.com/Database/MS_Access/Q_28584650.html#a40511158

I have a datediff using this:
Day Count: DateDiff("d",(Select Max(DisbRecdDate) From tblDisbursement_Amounts As T Where T.DisbRecdDate < tblDisbursement_Amounts.DisbRecdDate And T.Type=tblDisbursement_Amounts.Type),[DisbRecdDate])

It works fine but I need to tweak it.  

You can see from above that the DateDiff is the days between [DisbRecdDate] according to a Where condition of:   T.DisbRecdDate < tblDisbursement_Amounts.DisbRecdDate

What I need now is to add another condition for the DateDiff.  The condition is to return the DateDiff  from tblDates_impt."Execution Date of Financing Docs" to [DisbRecdDate].  The tblDates_impt."Execution Date of Financing Docs"date is #8/6/2014# as you can see in the tblDates_impt.  

Here is how I would need the output (see column far right, Day Count, the bold, underline 90)
Type      Disb Amt      Expected Disb Date      Total Disbursement      Disb RQ Date      Draw Down Rec'd                 Day Count
JBIC                      $0.00      04-Nov-14                                     $0.00                        8/6/2014                   11/4/2014                0
JBIC         $98,000,000.00      04-Nov-14                                   $98,000,000.00      10/1/2014      11/4/2014              90
JBIC         $25,000,000.00      11-Dec-14                                   $25,000,000.00      11/10/2014      12/11/2014                       37

The 90 is bolded and underlined in the tableabove (and this table is Q.Disbursement in the db) in last column "Day Count".  It is the Day Count from tblDates_impt."Execution Date of Financing Docs" to [DisbRecdDate].  The same would apply for the other .Type (NEXI, Uncovered)

thank you.  Please feel free to ask if not clear.
DayCountIssue-v2.accdb
pdvsaProject financeAsked:
Who is Participating?
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.

Gustav BrockCIOCommented:
Aren't these two Day Count questions identical?

> What I need now is to add another condition for the DateDiff ...

Add? If this condition is not replacing the current, what are the conditions for using either one?

/gustav
0
pdvsaProject financeAuthor Commented:
The are close but they are different.   You can see that there is a 90 day count in the above table that would not be in the output with the original DateDiff calculation.    So, I am looking to return a 90 for that record as shown above in the table far right column.  It is 11/4/14 - 8/6/14 (this date is in the tblDates,impt.  

Let me know if you have any other questions....thank you
0
pdvsaProject financeAuthor Commented:
its not replacing....it is adding to it.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
Sorry, I can't see any difference between the two questions except that in Day Count 2 is inserted:

"(which is: # 8/6/2014#)"

and:

"which is #11/4/14#)"

> its not replacing....it is adding to it.

/gustav
0
Gustav BrockCIOCommented:
The attached will do - but it relies on the first amount being zero ...

/gustav
DayCountIssue-v2.accdb
0

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
pdvsaProject financeAuthor Commented:
Ok here is the difference:
original:
Day Count: DateDiff("d",(Select Max(DisbRecdDate) From tblDisbursement_Amounts As T Where T.DisbRecdDate < tblDisbursement_Amounts.DisbRecdDate And T.Type=tblDisbursement_Amounts.Type),[DisbRecdDate])

the new one:
needs to reference:  
The tblDates_impt.[Execution Date of Financing Docs] date as #8/6/2014# as you can see in the tblDates_impt.  

tblDates_impt.[Execution Date of Financing Docs] can be seen as a START DATE of  #8/6/2014#.
to get 90 Day Count per the table above it would be #11/14/14# - #8/6/2014#.  

Maybe asking another way makes more sense:  How do you get the query to return a 90 in that second row, just above 37 with knowing the new information?  
here is the table again:  See the 90 above the 37 in last column Day Count:
Type      Disb Amt      Expected Disb Date      Total Disbursement      Disb RQ Date      Draw Down Rec'd                 Day Count
JBIC                      $0.00          04-Nov-14                                 $0.00                        8/6/2014                   11/4/2014                0
JBIC         $98,000,000.00      04-Nov-14                                 $98,000,000.00      10/1/2014                11/4/2014           90
JBIC         $25,000,000.00      11-Dec-14                                 $25,000,000.00      11/10/2014              12/11/2014            37


let me know what you need additional explanation.
0
pdvsaProject financeAuthor Commented:
Ok didn't see that DB posted for some reason.  Ok will check in a bit.  Thank you.
0
pdvsaProject financeAuthor Commented:
Perfect.  Thanks once again..
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.