time and colums
in [Getting Started]
|
Prev: Duplicate Counts
Next: Sorting a report
From: Tom on 29 Jan 2010 23:40 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 30 Jan 2010 07:50 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 30 Jan 2010 07:57 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 30 Jan 2010 09:06 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 31 Jan 2010 07:07
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 > > . > |