Incrementing time

C

Caffiend63

I have a table with a date time field formatted as general date. I want to
increment the each value in this field by 30 minutes, however when I do this
I finish up with a field of dates and times which appear correct (eg what was
3/2/1992 05:00:00 PM now reads 3/2/1992 05:30:00 PM) however if I try to run
a query using the new value, it doesn't exist. Converting it to a number
format shows that it does not exactly match the correct numerical value for
the date and time, so I am assuming something was wrong with my addition
statement ( [Workday]![Date_time]+#00:30:00# ). Can anyone tell me how to do
this correctly?
 
M

M.L. Sco Scofield

Try using the DateAdd function.

DateAdd("n", 30, [Workday]![Date_time])

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
C

Caffiend63

Thanks, it works in part but not for all cases. All the times appear fine,
but only some of them can be picked up in a query. I can't see any pattern to
the ones that work and those that don't.

M.L. Sco Scofield said:
Try using the DateAdd function.

DateAdd("n", 30, [Workday]![Date_time])

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


Caffiend63 said:
I have a table with a date time field formatted as general date. I want to
increment the each value in this field by 30 minutes, however when I do this
I finish up with a field of dates and times which appear correct (eg what was
3/2/1992 05:00:00 PM now reads 3/2/1992 05:30:00 PM) however if I try to run
a query using the new value, it doesn't exist. Converting it to a number
format shows that it does not exactly match the correct numerical value for
the date and time, so I am assuming something was wrong with my addition
statement ( [Workday]![Date_time]+#00:30:00# ). Can anyone tell me how to do
this correctly?
Click to expand...
Click to expand...
 
J

John Vinson

Caffiend63 said:
Thanks, it works in part but not for all cases. All the times appear fine,
but only some of them can be picked up in a query. I can't see any pattern to
the ones that work and those that don't.
Click to expand...

A Date/Time value is stored as a Double Float so it suffers the "roundoff
error" to which all floating point values are susceptible. Some numbers
cannot be represented *exactly* in this storage medium; although a date/time
cannot be displayed with finer resolution than the nearest second, it's
actually stored to a precision of a few microseconds. So it's likely that the
two date/times are off by less (maybe much less) than a second.

To compare times you should use a window, perhaps by using the Format()
function to convert the times to a text string in hh:nn:ss format and then
comparing those strings.

John W. Vinson/MVP
 
C

Caffiend63

That looks like it is the key - thanks John. Unfortunately I can't work out
how the Format function should be phrased. Could you help with an example of
this? Thanks.
 
M

M.L. Sco Scofield

Try:

Format([Workday]![Date_time], "hh:nn:ss")

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
Top