Solved

Day Count

Posted on 2014-12-21
9
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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