Incrementing time

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

Guest

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?
 
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
 
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...
 
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
 
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.
 
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
 
Back
Top