Solved

Day Count

Posted on 2014-12-21
9
125 Views
Last Modified: 2014-12-22
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
0
Comment
Question by:pdvsa
  • 5
  • 4
9 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40511744
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
 

Author Comment

by:pdvsa
ID: 40511758
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
 

Author Comment

by:pdvsa
ID: 40511760
its not replacing....it is adding to it.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40511810
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40511858
The attached will do - but it relies on the first amount being zero ...

/gustav
DayCountIssue-v2.accdb
0
 

Author Comment

by:pdvsa
ID: 40511871
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
 

Author Comment

by:pdvsa
ID: 40511910
Ok didn't see that DB posted for some reason.  Ok will check in a bit.  Thank you.
0
 

Author Closing Comment

by:pdvsa
ID: 40512045
Perfect.  Thanks once again..
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40512482
You are welcome!

/gustav
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

829 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