difference between date/time

[Guest]

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

First things first: Are the fields in question date/time data types at the
table level?

Next what do you mean by "Each record has a parameter in it"? Do you mean
that each record has a value in it OR this is a parameter query that prompts
you for dates when run?

Further by "Everything keeps coming back with no results" do you mean that
no records are returned or just nothing in the calculated field?

 

[Guest]

Yes, each record has a value and no records are returned.

First things first: Are the fields in question date/time data types at the
table level?

Next what do you mean by "Each record has a parameter in it"? Do you mean
that each record has a value in it OR this is a parameter query that prompts
you for dates when run?

Further by "Everything keeps coming back with no results" do you mean that
no records are returned or just nothing in the calculated field?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

Provide the SQL view. If you aren't returning any records then a calculated
column is not your immediate problem.
--
Duane Hookom
Microsoft Access MVP

Yes, each record has a value and no records are returned.

First things first: Are the fields in question date/time data types at the
table level?

Next what do you mean by "Each record has a parameter in it"? Do you mean
that each record has a value in it OR this is a parameter query that prompts
you for dates when run?

Further by "Everything keeps coming back with no results" do you mean that
no records are returned or just nothing in the calculated field?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

Whats the SQL View? Sorry, I'm not a advance user.

Provide the SQL view. If you aren't returning any records then a calculated
column is not your immediate problem.
--
Duane Hookom
Microsoft Access MVP

Yes, each record has a value and no records are returned.

First things first: Are the fields in question date/time data types at the
table level?

Next what do you mean by "Each record has a parameter in it"? Do you mean
that each record has a value in it OR this is a parameter query that prompts
you for dates when run?

Further by "Everything keeps coming back with no results" do you mean that
no records are returned or just nothing in the calculated field?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[fredg]

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

Re: > Everything keeps coming back with no results.
The above statement offers us no useful information.

Give us an example of the values in the 2 fields. What result should
be shown? What value is being shown? An #Error? Nothing? An incorrect
value? What?

Did you ever read VBA help on the DateDiff function?
The arguments are specific. You need to separate the 2 values with a
comma, not instruct Access to subtract one from the other. Access
knows that.

TimeDifferenceateDiff ("d", [Received Date Time],[Opened Date Time])

will determine, in days, the difference between the 2 values.
Note: the above will return a value of 1 day even if the [Received
Date Time] is 11/6/2007 11:59:59 PM and the [Opened DateTime] is
11/7/2007 12:00:01 AM, just 2 seconds later, because the date boundary
has been changed.

 

[Michel Walsh]

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP

 

[croy]

Whats the SQL View? Sorry, I'm not a advance user.

With your query in Design View, use the menus to go to
View|SQL View.

SQL is short for Structured Query Language. It was
initially developed by IBM, and is used extensively in the
database industry. It's actually what is created "under the
hood" by the query design grid in MS Access, and used to do
the real work of all your queries.

 

[Guest]

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[John W. Vinson]

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

This will return 0 in all instances. DateDiff("d", ...) returns the number of
passages through midnight between the dates; "m" returns the number of
passages into a new Month. If you want miNutes use "n".

It's not clear just what result you want!

John W. Vinson [MVP]

 

[Guest]

Here is my SQL view:
SELECT Lagtime.[Received Date Time], Lagtime.[Opened Date Time],
Lagtime.[Time Between Received and Opened], Lagtime.[Resolution Date],
Lagtime.[Closed Date Time], Lagtime.[Elapsed Time], Lagtime.[Department
Name], Lagtime.[(EA) Plant], Lagtime.[(EA) Floor], Lagtime.[Category
Description], Lagtime.[Module Description], Lagtime.[Source Description],
Lagtime.Status
FROM Lagtime;

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP

I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

The result I'm looking for is the lapse time between received and opened,
whether its minutes hours or days. I made a mistake and typed M and I
really typed N. No records are returning.

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

This will return 0 in all instances. DateDiff("d", ...) returns the number of
passages through midnight between the dates; "m" returns the number of
passages into a new Month. If you want miNutes use "n".

It's not clear just what result you want!

John W. Vinson [MVP]

 

[Guest]

Is Lagtime a table? Does it have any records in it?

It looks like Lagtime is a query that doesn't return any records. If it is a
query, what is the SQL view of Lagtime?

--
Duane Hookom
Microsoft Access MVP

Here is my SQL view:
SELECT Lagtime.[Received Date Time], Lagtime.[Opened Date Time],
Lagtime.[Time Between Received and Opened], Lagtime.[Resolution Date],
Lagtime.[Closed Date Time], Lagtime.[Elapsed Time], Lagtime.[Department
Name], Lagtime.[(EA) Plant], Lagtime.[(EA) Floor], Lagtime.[Category
Description], Lagtime.[Module Description], Lagtime.[Source Description],
Lagtime.Status
FROM Lagtime;

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP


I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

I have a feeling that you are placing the DateDiff() expression in the
criteria rather than creating a new column. Your SQL view should clear this
up.
--
Duane Hookom
Microsoft Access MVP

Here is my SQL view:
SELECT Lagtime.[Received Date Time], Lagtime.[Opened Date Time],
Lagtime.[Time Between Received and Opened], Lagtime.[Resolution Date],
Lagtime.[Closed Date Time], Lagtime.[Elapsed Time], Lagtime.[Department
Name], Lagtime.[(EA) Plant], Lagtime.[(EA) Floor], Lagtime.[Category
Description], Lagtime.[Module Description], Lagtime.[Source Description],
Lagtime.Status
FROM Lagtime;

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP


I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

What interval do I use if I want the results to consist of days and minutes?
Can I use "d", "n"?

All this time I forgot to put the expression in a new column, sorry about
that.

I have a feeling that you are placing the DateDiff() expression in the
criteria rather than creating a new column. Your SQL view should clear this
up.
--
Duane Hookom
Microsoft Access MVP

Here is my SQL view:
SELECT Lagtime.[Received Date Time], Lagtime.[Opened Date Time],
Lagtime.[Time Between Received and Opened], Lagtime.[Resolution Date],
Lagtime.[Closed Date Time], Lagtime.[Elapsed Time], Lagtime.[Department
Name], Lagtime.[(EA) Plant], Lagtime.[(EA) Floor], Lagtime.[Category
Description], Lagtime.[Module Description], Lagtime.[Source Description],
Lagtime.Status
FROM Lagtime;

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM


:

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP


I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?

 

[Guest]

Find one interval and stick with it if you want a numeric value. It's fairly
easy to use minutes and then divide by 60 to get hours or divide by (60*24)
to get days.

Doug Steele has a versatile function that returns a string at
http://www.accessmvp.com/djsteele/Diff2Dates.html.
--
Duane Hookom
Microsoft Access MVP

What interval do I use if I want the results to consist of days and minutes?
Can I use "d", "n"?

All this time I forgot to put the expression in a new column, sorry about
that.

I have a feeling that you are placing the DateDiff() expression in the
criteria rather than creating a new column. Your SQL view should clear this
up.
--
Duane Hookom
Microsoft Access MVP

Here is my SQL view:
SELECT Lagtime.[Received Date Time], Lagtime.[Opened Date Time],
Lagtime.[Time Between Received and Opened], Lagtime.[Resolution Date],
Lagtime.[Closed Date Time], Lagtime.[Elapsed Time], Lagtime.[Department
Name], Lagtime.[(EA) Plant], Lagtime.[(EA) Floor], Lagtime.[Category
Description], Lagtime.[Module Description], Lagtime.[Source Description],
Lagtime.Status
FROM Lagtime;


:

I tried
DateDiff ("d", [Received Date Time] , [Opened Date Time]) & also
DateDiff ("m" , [Received Date Time] , [Opened Date Time])

and I still get no records returning after running the query. Here is an
example:

Received Date Time Opened Date Time
09/17/2007 15:49:24 PM 09/17/2007 3:55:24 PM
09/20/2007 09:55:00 AM 09/20/2007 11:48:00 AM
09/20/2007 14:40:33 PM 09/24/2007 2:40:33 PM


:

You use a - while a coma is needed. Use


DateDiff ("d", [Received Date Time] , [Opened Date Time])


instead of your initial syntax:

DateDiff ("d", [Received Date Time] - [Opened Date Time])




Vanderghast, Access MVP


I have searched and found some good info but nothing is working for me.
Please help.

I have a query that has "Received Date Time and "Opened Date Time" fields.
Each record has a parameter in it and formatting is as follows:
m/d/y space h/m/s AM/PM.

I'm trying to calucate the difference in the two and have tried the
following expressions:
DateDiff ("d", [Received Date Time] - [Opened Date Time])
Diff: [Received Date Time] - [Opened Date time]

Everything keeps coming back with no results. What am I doing wrong?