From: Tom on
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

From: Jeff Boyce on
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.


"Tom" <Tom(a)discussions.microsoft.com> wrote in message
news:B20190C5-2796-4FD1-A89D-2F219298C1FF(a)microsoft.com...
>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
>


From: Arvin Meyer [MVP] on
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Tom" <Tom(a)discussions.microsoft.com> wrote in message
news:B20190C5-2796-4FD1-A89D-2F219298C1FF(a)microsoft.com...
>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
>


From: KenSheridan via AccessMonster.com on
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 wrote:
>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

--
Message posted via http://www.accessmonster.com

From: Tom on
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" wrote:

> 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 wrote:
> >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
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Duplicate Counts
Next: Sorting a report