time and colums
in [Getting Started]
|
Prev: Duplicate Counts
Next: Sorting a report
From: KenSheridan via AccessMonster.com on 31 Jan 2010 08:49 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 Tom wrote: >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 -- Message posted via http://www.accessmonster.com
From: Tom on 31 Jan 2010 10:24 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 "KenSheridan via AccessMonster.com" wrote: > 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 > > Tom wrote: > >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 > > -- > Message posted via http://www.accessmonster.com > > . >
From: Tom on 13 Feb 2010 09:36 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
From: KenSheridan via AccessMonster.com on 13 Feb 2010 11:54 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 wrote: >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1
From: Tom on 13 Feb 2010 12:49
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 |