C
Colin Small
In an ADP project, a time (eg a field called T1 and a value of midday) is
entered in a textbox on a form, bound to a SQl 2000 table. Query analyser
shows that "cast(t1 as real)" has a value 0.5 - as expected.
However, when T1 is read back into the form, cdbl(T1) has the value 2.5.
Sql actually returns #01/01/1900 12:00# and cdbl(cdate("01/01/1900 12:00"))
= 2.5
This is inevitably a "feature" due to the difference between Access and SQL
dates, but I haven't noticed it specifically mentioned anywhere.
Orignally I wanted to simply add a date to a time, so I could do a
conditional format based on a comparison to now(), but the result was offset
by 2. dateadd() rounds any attempt to use this! One workaround is to use
+timevalue(time).
Has anyone seen a more systematic way of dealing with this issue? Unless I
have mistaken my analysis, this breaks the programming norm of - you get
back what you store. I thought I would have seen it mentioned more
prominently!
Colin
entered in a textbox on a form, bound to a SQl 2000 table. Query analyser
shows that "cast(t1 as real)" has a value 0.5 - as expected.
However, when T1 is read back into the form, cdbl(T1) has the value 2.5.
Sql actually returns #01/01/1900 12:00# and cdbl(cdate("01/01/1900 12:00"))
= 2.5
This is inevitably a "feature" due to the difference between Access and SQL
dates, but I haven't noticed it specifically mentioned anywhere.
Orignally I wanted to simply add a date to a time, so I could do a
conditional format based on a comparison to now(), but the result was offset
by 2. dateadd() rounds any attempt to use this! One workaround is to use
+timevalue(time).
Has anyone seen a more systematic way of dealing with this issue? Unless I
have mistaken my analysis, this breaks the programming norm of - you get
back what you store. I thought I would have seen it mentioned more
prominently!
Colin