time and colums

[Tom]

I have one table with 3 columns and would like to subtract same day time form
one another. (example - time clock)

My columns are as follows: barcode, Qty, date_time.
I understand how to use the "Datediff" expression with 2 columns but as you
see, I only have one that is used for the date and time.
Ex: 5776 1 1/26/2010 07:30:00 AM
5776 1 1/26/2010 03:30:00 pm
Whats the best way to handle this?

Thank you!

Tom

 

[Jeff Boyce]

Tom

For Access to be able to subtract a field in one record from a field in
another record, it needs to know which records.

How do YOU know which records?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Arvin Meyer [MVP]]

Use a query column for the DateDiff function. You'll need a source for the
second date/time. You can use the Now() function to get the current time
from the computer clock. You can get the time from another field in the
database where you can join the tables. You can calculate a field from a
known point, like yesterday at 6:00 AM. Or you can use an external source
via a serial or USB cable.

 

[KenSheridan via AccessMonster.com]

Tom:

Are you saying you want to subtract the latest previous date/time value from
the current one to get the elapsed time? If so the following function
returns the elapsed time between two data time values in the format hh:nn:ss,
with the option of also showing days if the hours returned might be 24 or
more

Public Function TimeDuration( _
varFrom As Variant, _
varTo As Variant, _
Optional blnShowDays As Boolean = False)

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

If Not IsNull(varFrom) And Not IsNull(varTo) Then
dblDuration = varTo - varFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If
End If

End Function

Call it in query with a subquery to return the latest prior date time value
as its first argument, like so:

SELECT barcode, Qty, date_time,
TimeDuration(
(SELECT MAX(date_time)
FROM YourTable As T2
WHERE T2.date_time < T1.date_time),
date_time) As ElapsedTime
FROM YourTable As T1
ORDER BY date_time DESC;

If you want the elapsed times per barcode correlate the subquery on this
column also:

SELECT barcode, Qty, date_time,
TimeDuration(
(SELECT MAX(date_time)
FROM YourTable As T2
WHERE T2.barcode = T1.barcode
AND T2.date_time < T1.date_time),
date_time) As ElapsedTime
FROM YourTable As T1
ORDER BY barcode, date_time DESC;

If you want the elapsed time in a single time unit, e.g. minutes, you can of
course use the DateDiff function in the same way as the above function.

Ken Sheridan
Stafford, England

 

[Tom]

Ken,
Your solution seems to be right on the money!
But I am new to sql and am having problems putting the code in the proper
places to make it work.

I did get it to work one time then i received an error of " Circular
refference called by "tbltbl" ( the name of the stored input data from
scanner)

After reading the orignal expression, I made a duplicate table named
"yourtable"
Now I get an error of "Undefined function 'timeduration' in expression

Any idea's?

Thanks to all who have helped!

Tom

 

[KenSheridan via AccessMonster.com]

Tom:

Firstly paste the function into any standard module. If it’s a new module
save it under a different name from the function, e.g. mdlDateTimeStuff.

Then open the query designer and switch to SQL view. Paste in the SQL
statement in place of whatever is there and change the two instances of
'YourTable' to whatever is the real table name. Assuming the column names
are exactly as you posted them you shouldn't need to change anything else.

If it still doesn't recognize the function, which can sometimes happen when
a function is pasted in, change the name of the function to something
different like TimeElapsed. You'll need to change it in each place it occurs
in the function's code, and in the SQL statement of the query.

Ken Sheridan
Stafford, England

Ken,
Your solution seems to be right on the money!
But I am new to sql and am having problems putting the code in the proper
places to make it work.

I did get it to work one time then i received an error of " Circular
refference called by "tbltbl" ( the name of the stored input data from
scanner)

After reading the orignal expression, I made a duplicate table named
"yourtable"
Now I get an error of "Undefined function 'timeduration' in expression

Any idea's?

Thanks to all who have helped!

Tom

Tom:

[quoted text clipped - 83 lines]

 

[Tom]

Ken,

Thank You for the fast reply!
your solution was a bulls eye!
renaming the module to "mdDateTimesStuff" was the problem.

Thank You so very much

Tom:

Firstly paste the function into any standard module. If it’s a new module
save it under a different name from the function, e.g. mdlDateTimeStuff.

Then open the query designer and switch to SQL view. Paste in the SQL
statement in place of whatever is there and change the two instances of
'YourTable' to whatever is the real table name. Assuming the column names
are exactly as you posted them you shouldn't need to change anything else.

If it still doesn't recognize the function, which can sometimes happen when
a function is pasted in, change the name of the function to something
different like TimeElapsed. You'll need to change it in each place it occurs
in the function's code, and in the SQL statement of the query.

Ken Sheridan
Stafford, England

Ken,
Your solution seems to be right on the money!
But I am new to sql and am having problems putting the code in the proper
places to make it work.

I did get it to work one time then i received an error of " Circular
refference called by "tbltbl" ( the name of the stored input data from
scanner)

After reading the orignal expression, I made a duplicate table named
"yourtable"
Now I get an error of "Undefined function 'timeduration' in expression

Any idea's?

Thanks to all who have helped!

Tom

Tom:

[quoted text clipped - 83 lines]

Tom

 

[Tom]

How could I eliminate the "day" factor and not show any results over a 24
hour time span?

I am calculating snow removal job times as well as my employee start and end
time on a "as needed" basis that would never span past an 8 hour day

Thank you very much!!!

Tom

 

[KenSheridan via AccessMonster.com]

Tom:

Are you saying ignore any results where the difference between the times is
more than 8 hours so as not to return those where the query would be
subtracting the previous end time from a start time? If so try this:

SELECT barcode, Qty, date_time,
TimeDuration(
(SELECT MAX(date_time)
FROM YourTable As T2
WHERE T2.barcode = T1.barcode
AND T2.date_time < T1.date_time),
date_time) As ElapsedTime
FROM YourTable As T1
WHERE DateDiff("h",
(SELECT MAX(date_time)
FROM YourTable As T3
WHERE T3.barcode = T1.barcode
AND T3.date_time < T1.date_time),
date_time)) <= 8
ORDER BY barcode, date_time DESC;

You might want to increase the restriction from 8 to something higher to
allow some leeway. The value needs to be greater than the maximum worked
time for a shift, and less than the minimum time between an employee ending
one shift and starting the next. If I've misunderstood perhaps you could
give an example of some rows and what you'd want returned.

Ken Sheridan
Stafford, England

 

[Tom]

Ken,

You are dead on! thank you so very much.

I was trying to wright the queries using the datediff but couldn't figure
out how or where to place it in the sql without errors.

May I donate to your cause?

Thanks again

Tom

 

[KenSheridan via AccessMonster.com]

Tom:

Its good to know I still have one or two grey cells functioning. I'm glad to
have helped, but in answer to your question, the following is always in need
of any little assistance which can be given:

http://www.fishermensmission.org.uk/

It may seem an odd choice for someone who lives about as far from the sea as
its possible to get in the UK (which admittedly isn't very far) but both my
wife's and my family have strong maritime ties.

Ken Sheridan
Stafford, England