Solved

Day Count

Posted on 2014-12-21
9
120 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I Flash a mandatory field in Access Form? 13 48
Access Update Query 1 20
Help with DoEvents 8 28
Progress bar in access form 10 17
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 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