Calculate total number of days between two dates in a query

[tom_broke]

Hello all,

I have a bit of a challenge here, I want to calculate the total number of days between two dates, but I want to show this total everytime we run the query. One field is DateReceived, the other is DateClosed, I would like to be able to generate a third column within the query that performs the calculation for this two values.

Thank you for your assistance...

 

[jatfill]

The basic interpretation for your third column would be as follows (the "d" tag measures the date difference in days):

DateDiff("d",[DateReceived],[DateClosed])

If you want to add an exception that handles records with no closed date, use this one (optionally, if you just want to look at records where there is a closed date, add "Is Not Null" as the criteria for ClosedDate and use the top expression):

IIf(IsNull([DateClosed]),"Not Closed Message",DateDiff("d",[DateReceived],[DateClosed]))


Good Luck...

 

[tom_broke]

Thanks, it worked like a charm!!!

 

[Esc:Smitty]

dates

The basic interpretation for your third column would be as follows (the "d" tag measures the date difference in days):

DateDiff("d",[DateReceived],[DateClosed])

If you want to add an exception that handles records with no closed date, use this one (optionally, if you just want to look at records where there is a closed date, add "Is Not Null" as the criteria for ClosedDate and use the top expression):

IIf(IsNull([DateClosed]),"Not Closed Message",DateDiff("d",[DateReceived],[DateClosed]))


Good Luck...

I tried your formula, but it comes back with "its too complex". I just changed the "date closed" to my columan header which is closeddate and the date received to recvddate. Due to the headings of my columns. Any suggestions?

IIf(IsNull([ClosedDate]),"Not Closed Message",DateDiff("d",[RecvdDate],[ClosedDate]))

 

[boblarson]

I tried your formula, but it comes back with "its too complex". I just changed the "date closed" to my columan header which is closeddate and the date received to recvddate. Due to the headings of my columns. Any suggestions?

IIf(IsNull([ClosedDate]),"Not Closed Message",DateDiff("d",[RecvdDate],[ClosedDate]))

If you are using this in a query (which is what the original message was about) you need:

MyNewDate:IIf(IsNull([ClosedDate]),"Not Closed Message",DateDiff("d",[RecvdDate],[ClosedDate]))

And ClosedDate and RecvdDate MUST be in the query.

 

[Esc:Smitty]

If you are using this in a query (which is what the original message was about) you need:

MyNewDate:IIf(IsNull([ClosedDate]),"Not Closed Message",DateDiff("d",[RecvdDate],[ClosedDate]))

And ClosedDate and RecvdDate MUST be in the query.

Bob,
Thanks for getting back to me so quick I was able to get it to work finally, but the only problem that I'm having is that, when I run the query, It will ask for the current date, I want it to just atomatically grab the current date and put the value in the box of how many days there are between the date the ticket was opened and today, due to the fact the ticket is still in an open status. I've tried, Now(), Today() both and still can't keep it from asking me for a date. When I enter today's date, it runs great. here is what both of the the string looked like. Again thanks!

DaysOpen: IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],[Now()]))

DaysOpen: IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],[Today()]))

 

[Esc:Smitty]

Bob,
Thanks for getting back to me so quick I was able to get it to work finally, but the only problem that I'm having is that, when I run the query, It will ask for the current date, I want it to just atomatically grab the current date and put the value in the box of how many days there are between the date the ticket was opened and today, due to the fact the ticket is still in an open status. I've tried, Now(), Today() both and still can't keep it from asking me for a date. When I enter today's date, it runs great. here is what both of the the string looked like. Again thanks!

DaysOpen: IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],[Now()]))

DaysOpen: IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],[Today()]))

OK, here is the next problem, It shows up fine in Access when I run the query, but now when I export it to excel, I loose all the numbers from the column. Any way to make sure this exports?

 

[raskew]

Hi -

See several problems:
1. Date functions in Access include:
date() returns the current date
now() returns the current date and time
today() is an Excel function not supported in Access

Here are examples from the immediate (debug) window

? date()
5/31/2007 

? now()
5/31/2007 10:42:02 AM
 
? cdbl(now())
 39233.4459606481
 
? today()
[i]Sub or function not defined[/i]

2, Date() is probably the most logical function in your situation. Regardless of using Date() or Now(), these are functions, not fields. Remove the square brackets surrounding these functions.

3. The Iif() function requires:
a. A situation (e.g. IsNull([Closeddate]),
b. Solution if True
c. Solution if False

You have provided a solution only if the situation is true. While it may appear to process, as you've discovered it returns some very strange results. Look up Iif() in the help file.

HTH - Bob

 

[Esc:Smitty]

Brackets and "Date"

Hi -

See several problems:
1. Date functions in Access include:
date() returns the current date
now() returns the current date and time
today() is an Excel function not supported in Access

Here are examples from the immediate (debug) window

? date()
5/31/2007 

? now()
5/31/2007 10:42:02 AM
 
? cdbl(now())
 39233.4459606481
 
? today()
[i]Sub or function not defined[/i]

2, Date() is probably the most logical function in your situation. Regardless of using Date() or Now(), these are functions, not fields. Remove the square brackets surrounding these functions.

3. The Iif() function requires:
a. A situation (e.g. IsNull([Closeddate]),
b. Solution if True
c. Solution if False

You have provided a solution only if the situation is true. While it may appear to process, as you've discovered it returns some very strange results. Look up Iif() in the help file.

HTH - Bob

Bob!

Thanks for responding. Well here is the problem, (1) I can't remove the brackets, every time I do and save it, they are reinserted!

(2) If I use "Now()" instead of "Date()", The days open column is blank. Thanks for your help. I'm so frustrated with this and I know its something simple.

Jim

 

[boblarson]

The reason your brackets come back is that you probably have either code or a field that is named the same as the Access function.

 

[Esc:Smitty]

lost

The reason your brackets come back is that you probably have either code or a field that is named the same as the Access function.

Ok, now I'm really lost.

 

[Esc:Smitty]

There are 2 fields in my queary that are named the same as in the database, that would be "closeddate" and "Recvddate" Is that what you are speaking of?

DaysOpen: IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],Now()))

 

[boblarson]

No, I was talking about the words DATE, TODAY, or NOW.

 

[raskew]

Hi -

How about posting the complete query (and preferably the table structure) so we can take a look.

Bob

 

[Esc:Smitty]

Hi -

How about posting the complete query (and preferably the table structure) so we can take a look.

Bob

How do I post it, I don't know how to save it in order for me to post it.

 

[raskew]

Hi -

You're way in "over your head".

Need to do a little more research.

Bob

 

[Sham]

hi i've got a similar problem - i want to calculate the date difference between two dates in "yy-mm" format.
I want to get the length of service so number of years and months between hire date and termination date.
Can anyone help?

 

[Esc:Smitty]

Hmm

Hi -

You're way in "over your head".

Need to do a little more research.

Bob

So, Bob, are you saying you don't know how to do it either?

 

[Esc:Smitty]

Her's the SQL version

SELECT HeatBpam_CallLog.CallID, IIf(IsNull([Closeddate]),DateDiff("d",[RecvdDate],Now())) AS DaysOpen, HeatBpam_CallLog.CustID, HeatBpam_CallLog.CallType, HeatBpam_CallLog.SubCallType, HeatBpam_CallLog.RecvdDate, HeatBpam_CallLog.RecvdTime, HeatBpam_CallLog.CallStatus, HeatBpam_CallLog.Tracker, HeatBpam_Asgnmnt.Assignee, HeatBpam_CallLog.AssignedTo, HeatBpam_CallLog.Vendor
FROM HeatBpam_Asgnmnt RIGHT JOIN HeatBpam_CallLog ON HeatBpam_Asgnmnt.CallID = HeatBpam_CallLog.CallID
WHERE (((HeatBpam_CallLog.CallStatus) Like "o*") AND ((HeatBpam_CallLog.Priority) Like "4") AND ((HeatBpam_Asgnmnt.Resolution)=" ") AND ((HeatBpam_CallLog.ClosedDate)=" "))
ORDER BY HeatBpam_CallLog.RecvdDate;

Heres what it looks like, as you can see the "days open" column has the intended result. But when exported to excel, this column is blank:

CallID DaysOpen CallType SubCallType RecvdDate RecvdTime
208 560 Application Request CCMS - Request 2005-11-21 13:17:13
1528 432 Application Request Intranet - Request 2006-03-29 14:03:02
3467 382 ITIM Security-ITIM 2006-05-18 07:19:32
4109 366 ITSDM Miscellaneous-ITSDM 2006-06-03 11:47:08
4119 366 ITSC Miscellaneous-ITSC 2006-06-03 14:32:15
4119 366 ITSC Miscellaneous-ITSC 2006-06-03 14:32:15
5527 329 Software Outlook 2003 2006-07-10 09:43:18
7031 293 Hardware Laptop 2006-08-15 16:08:00
7449 280 ITEPM Miscellaneous-ITEPM 2006-08-28 09:06:17

 

[boblarson]

How do I post it, I don't know how to save it in order for me to post it.

You need to go to Tools > Database Tools > Compact and Repair and let it compact and repair. Then, you use WinZip, or something similar, to zip the file for upload. The final compacted and zipped file should be 393KB or less to post here.

Then, use the Go Advanced button in the posts here and you can go down near the bottom of the page and click on Manage Attachments. From there you should be able to work your way through.