update query for date & time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a new field in a table that combines the calendar date and
military time from two other fields. Is this possible? I have tried to
combine the fields in several ways but my knowldege base for such expressions
is limited.

thanks,
 
Oh, I should state that the new field is in date/time format, the calendar
field is in date/time format, but the time field is in number format.
 
So how does the number represent the time

Is 08:15:02 stored as 815? or is it stored as the number of seconds past
midnight? Or is it stored as the number of hours and fractional hours 8.25?

How about 22:43:17?

More detail is needed to give you correct advice.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

The number comes from a datalogger every 15 minutes and is stored as hours
and minutes in one value. For example, a sequence in time would be recorded
as 0, 15, 30, 45, 100 (where 100 is the 1:00 hour) and so on. Such that the
last two digits of the number are the minutes and the first one/two digits
are the hour.

I've been messing around with differnt functions and looking at the
discussion group questions/replies but have not found this one.

Thanks for your help
 
So 11:59 is stored as 1159.

to get the Minutes you can use
1159 mod 100

To get the hour
Int(1159/100)

So the time in minutes would be

Int(TimeField/100) * 60 + (TimeField Mod 100)

So it would seem that one way to combine the fields would be:

DateAdd("n",Int(TimeField/100) * 60 + (TimeField Mod 100),DateField)

Note the M is Month, so you need to use N for minutes in the DateAdd
expression

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thanks John. It worked.
--
Brian


John Spencer said:
So 11:59 is stored as 1159.

to get the Minutes you can use
1159 mod 100

To get the hour
Int(1159/100)

So the time in minutes would be

Int(TimeField/100) * 60 + (TimeField Mod 100)

So it would seem that one way to combine the fields would be:

DateAdd("n",Int(TimeField/100) * 60 + (TimeField Mod 100),DateField)

Note the M is Month, so you need to use N for minutes in the DateAdd
expression

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
 
Back
Top