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.

 

[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

 

[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

 

[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