Date and Time Difference

[ladybug via AccessMonster.com]

Here is the SQL view to my query:

SELECT tblDetail.Date, tblProductQuality.Autonumber AS LRN, tblProductQuality.
OrigNumber AS UID, [PQDateFaxed]+[PQTimeFaxed] AS Faxed
FROM tblDetail INNER JOIN tblProductQuality ON tblDetail.Number =
tblProductQuality.OrigNumber
WHERE (((tblProductQuality.Autonumber) Is Not Null) AND ((tblProductQuality.
ProductQuality)=-1));


I need to know how many days, hours, minutes, seconds between tblDetail.Date
and Faxed([PQDateFaxed]+[PQTimeFaxed].

Can someone please help me with the next steps? Can I add something to the
query to get this?

 

[Jeff Boyce]

Why are you recording separate date and time fields? Access offers a
Date/Time datatype that holds both in the same field. It might make using
the DateDiff() function a bit easier...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

I thought we resolved this last week.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[ladybug via AccessMonster.com]

Sorry, i was unable to make any of the suggestions work. I thought if I gave
the SQL it may be easier for someone to assist me.

I thought we resolved this last week.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Here is the SQL view to my query:

[quoted text clipped - 10 lines]

Can someone please help me with the next steps? Can I add something to the
query to get this?

 

[ladybug via AccessMonster.com]

Are you referring to why "Faxed" is two fields? I inherited this database.
I can only work with what I have.

Why are you recording separate date and time fields? Access offers a
Date/Time datatype that holds both in the same field. It might make using
the DateDiff() function a bit easier...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the SQL view to my query:

[quoted text clipped - 14 lines]

the
query to get this?

 

[John Spencer]

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ladybug via AccessMonster.com]

I tried this one in my query:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)

Changed it to ?Diff2Dates("dhns", [Date], [Faxed On])

I received a Syntax Error and then it highlighted Diff2Dates

Any other thoughts?Thank you for your help!

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Here is the SQL view to my query:

[quoted text clipped - 10 lines]

Can someone please help me with the next steps? Can I add something to the
query to get this?

 

[John Spencer]

Did you go to the site and copy the function and put it into a VBA module of
your database?

Did you save the module with a name other than Diff2Dates? The module cannot
have the same name as the function. I usually start all module names with
"mod_" to insure the name is unique (and I don't start the name of any other
object type with "mod_".).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I tried this one in my query:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)

Changed it to ?Diff2Dates("dhns", [Date], [Faxed On])

I received a Syntax Error and then it highlighted Diff2Dates

Any other thoughts?Thank you for your help!

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[ladybug via AccessMonster.com]

This may be way over my head. I have never worked with Modules. If you
don't have time to help me I understand.

I did copy from the site this:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)

Now I went to "Create" and selected Module. VBA opened and I see Option
Compare Database
I pasted this ?Diff2Dates("dhns", [Date], [Faxed On])
it changed the "?" to "Print"

I have no idea what to do at this point? How does my query come in to play?

I copied it, but I put this in

Did you go to the site and copy the function and put it into a VBA module of
your database?

Did you save the module with a name other than Diff2Dates? The module cannot
have the same name as the function. I usually start all module names with
"mod_" to insure the name is unique (and I don't start the name of any other
object type with "mod_".).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I tried this one in my query:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)

[quoted text clipped - 8 lines]

 

[John W. Vinson]

This may be way over my head. I have never worked with Modules. If you
don't have time to help me I understand.

I did copy from the site this:
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)

Now I went to "Create" and selected Module. VBA opened and I see Option
Compare Database
I pasted this ?Diff2Dates("dhns", [Date], [Faxed On])
it changed the "?" to "Print"

I have no idea what to do at this point? How does my query come in to play?

Debbie, what that line is doing is trying to call a VBA function named
"Diff2Dates". There is no such function built in to Access.

Instead you need to go to Jeff's website as he posted. Find the VBA code there
(it's helpful if you understand what it's doing but not essential!). Open the
Modules tab in your database window, and create a new Module. Copy and paste
the VBA code (it should start with Public Function Diff2Dates(... and end
with End Function) into the VBA editor window. Select Debug... Compile <my
database name> from the menu. Save the module as basDates (DON'T use
Diff2Dates as the module name).

Then you should be able to put

HowLong: Diff2Dates("dhns", [Date], [Faxed On])

in a vacant Field cell in a Query.

One note: DON'T use Date as a fieldname in your table. It can and will be
confused with the builtin Date() function, which reads the system clock.

 

[John W. Vinson]

Instead you need to go to Jeff's website as he posted.

oops, sorry, John's website.

 

[John Spencer]

Nope, still not right. She could go to the website if referenced to get
code from Doug Steele/Graham Seach.

Sorry John. Very, very, very seldom do I get the opportunity to correct
you and even then it is for something that is trivial.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================