Calculate total number of days between two dates in a query (1 Viewer)

T

tom_broke

Guest
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

Registered User.
Local time
Today, 13:23
Joined
Jun 4, 2001
Messages
150
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...
 
T

tom_broke

Guest
Thanks, it worked like a charm!!!
 

Esc:Smitty

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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

Smeghead
Local time
Today, 10:23
Joined
Jan 12, 2001
Messages
32,059
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

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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()]))
 
Last edited:

Esc:Smitty

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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

AWF VIP
Local time
Today, 12:23
Joined
Jun 2, 2001
Messages
2,734
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
Code:
? 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

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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
Code:
? 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

Smeghead
Local time
Today, 10:23
Joined
Jan 12, 2001
Messages
32,059
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

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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

Smeghead
Local time
Today, 10:23
Joined
Jan 12, 2001
Messages
32,059
No, I was talking about the words DATE, TODAY, or NOW.
 

raskew

AWF VIP
Local time
Today, 12:23
Joined
Jun 2, 2001
Messages
2,734
Hi -

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

Bob
 

raskew

AWF VIP
Local time
Today, 12:23
Joined
Jun 2, 2001
Messages
2,734
Hi -

You're way in "over your head".

Need to do a little more research.

Bob
 

Sham

Registered User.
Local time
Today, 17:23
Joined
May 31, 2007
Messages
11
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

New member
Local time
Today, 10:23
Joined
May 23, 2007
Messages
9
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
 
Last edited:

boblarson

Smeghead
Local time
Today, 10:23
Joined
Jan 12, 2001
Messages
32,059
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.
 

Users who are viewing this thread

Top Bottom