Time Calculation Using Current Time

[Learn2010]

I need a Select Query to display data on a form. When an order is appended to my table the field named Printed is updated with the time it was appended. When my form opens it needs to display the field Printed and a field I call MinutesFromPrint. This field needs to display the total minutes elapsed from the time in the Printed field to the current time. My expression is not working.

MinutesFromPrint: DateDiff("n",[Printed],Now())

Here are a couple of samples of my results when I run the query at 7:49 A.M.:

Printed MinutesFromPrint
2:35 60366554
5:07 60366402

Can anyone help me correct this?

Thank you.

 

[Minty]

How have you set and stored [Printed]? What is the data type for [Printed] ?

 

[Learn2010]

[Printed] is a Date/Time field formatted as Short Time. Default is Time()

 

[spikepl]

Your expression delivers exactly what you asked of it. You are comparing a time (with 0 as date element) with Now , which is today's date and time. So it calculated the number of minutes from Dec 30, 1899 at 02:35 AM (which correpsonds to date 0) until Now(). Compare time with time or date with date. A useful function could be TimeValue.

 

[Learn2010]

How should that expression look then?

 

[Minty]

Assuming your Printed field is a Date Time as you stated - you need to format the answer as a number you will get your result. If you format the result as a date it won't make any sense. (309 as a date will be sometime in 1890 as Spike pointed out)

 

[spikepl]

You did get a hint - use it.

 

[Minty]

I would advise setting Printed to DateTime not a Time - then your calculation will work if the print job goes past midnight as well

 

[Learn2010]

I used this to get my expression. What did I miss?

http://allenbrowne.com/casu-13.html

Calculating elapsed time

How do you calculate the difference between two date/time fields, such as the hours worked between clock-on and clock-off?
Use DateDiff() to calculate the elapsed time. It returns whole numbers only, so if you want hours and fractions of an hour, you must work in minutes. If you want minutes and seconds, you must get the difference in seconds.
Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes: DateDiff("n", [StartDateTime], [EndDateTime])

 

[Minty]

You missed the fact that datediff takes the difference between whole DATES. You have stored a time. Unfortunately that time also has a date value you can't see which is 30 Dec 1899.

If you want to see it change the field type for [Printed] on your form to long date and see what you get.

 

[Learn2010]

I did some back tracking and was able to get the [Printed] field coming into the database as a General Date, which gives me the date and time. The expression now works.

Thanks.

 

[Minty]

You should mark the thread as solved - it will help others to find a solution.