Solved

Day Count

Posted on 2014-12-21
9
126 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 50

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40512482
You are welcome!

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

685 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