extract time from datetime

Question

I have a table with a datetime field.  I want to update the time field to be just the extract time from the datetime field as a number.  For example, if I have 08/27/2013 13:15:00, I want 1315 in the time field.  I am not concerned about leading zeroes, therefore, 0:00 can be 0, 0:15 can be 15 and so on.  I tried timevalue; however, it gives me decimals (that to me do not make sense)!?  Please help!

Answer 1

Access stores date/time values as numbers - a count of days and fractions of a day (times) since midnight, December 30, 1899. For instance:

now; cdbl(now)

7/7/2015 5:27:14 PM  42192.7272453704 

At the time I ran this, it was 42192 days since that long-ago date, and 72.7% of a day more.

So a time only value corresponds to a point in time back in the end of the 19th Century. 

What do you plan to DO with this time? Don't confuse data storage with data display; for most purposes you would be better off just storing the date and time together. You can display the date/time value using a Format property of "hhnn" - to give 1315; if you want just an integer number of minutes since midnight, you could use either

Val(Format([datetimefield], "hhnn")

or

DateDiff("n", DateValue([datetimefield]), [datetimefield])

Answer 2

Well, depending of course on your database's needs, I'd suggest taking advantage of Access' very flexible date/time datatype. It sorts chronologically; it can be indexed for efficiency; it allows easy searching of date ranges (all records in a particular minute or a particular century or whatever); and it can be formatted to display pretty much however you want. Storing integers is just going to get you into constant programming effort to do conversions. But if it works for you, go right ahead!

Answer 3

Thanks John for the education and the answer.  At the present I am just storing the day and time separately as integers.  I should have remembered the Val function!  doh!