Date add function not working in some cases.

[behnam]

Hi, when i use the date add function to subtract three hours from a long type time filed (ie. 10:30:22 PM) its works but dosent work when the time is 2:59:59 AM and under because when i subtract 3 hours it dosent go to 11:59:59 PM like it should instead, i get gibberish like 10/31/1899. the code is

Text436 = DateAdd("h", -3, [start time1])

can anyone help me fix this issue, thanks.

[Missinglinq]

Are you just getting

10/31/1899

or

10/31/1899 11:59:59 PM

Linq ;0)>

[John_G]

If the data that is in [start time1] is ONLY the time, and no date part, that is the cause of the problem. Here is what I did in the immediate pane, and you can see that I replicated the problem:

x=#2:59:59 AM#
?x
2:59:59 AM
?dateadd("h",-3,x)
29/12/1899 11:59:59 PM

The time part is correct, but the date part is wrong because there wasn't one there to begin with.

In order to get what you want, you will have to include the date in [start time1], even if you don't display it.

John

[Missinglinq]

As John said, Access defaults to 29/12/1899 (or 12/29/1899, in this country) when the date portion is missing. If all you're really only interested is the time, you could use

Me.Text416 = Format(DateAdd("h", -3, [start time1]), "hh:mm:ss am/pm")

And it will only return/display the Time portion.

Linq ;0)>