problems with DateTime Fields after using UPDATE query in access 2002

  • Thread starter Thread starter ofir
  • Start date Start date
O

ofir

Hello.

My name is Ofir and my problem goes like this.
I use and Update query in ACCESS:

PARAMETERS [time] DateTime;
UPDATE PIT_TAGS SET PT_TIME = PT_TIME + [time]
WHERE YEAR(PT_DATE)=1996;

The problem is that after running this query, when running a SELECT
query it seems that the PT_TIME field is not recognized anymore as a
DateTime field because a simple WHERE query ( somthing like WHERE
(PT_TIME<=[sunrise] OR PT_TIME>=[sunset])) is not giving the right
results while records which weren't updated are giving the right
results.

Does anyone know what might be wrong?

thanks, Ofir
 
What value was "[time]"? Dates and times are actually stored as numbers from
a base reference point. The date part is the whole number part and the time
is the decimal portion.

Some results of this from the Immediate Window:
?#2:00:00 am# + 3
1/2/1900 2:00:00 AM

?#2:00:00 pm# + #3:00:00 pm#
12/31/1899 5:00:00 AM

?#2:00:00 am# + #3:00:00 am#
5:00:00 AM

?Format(CDate(0.0), "dd mmm yyyy ttttt")
30 Dec 1899 12:00:00 AM

As you can see, adding 3 adds 3 days. Adding PM times creates a number >= 1,
so a dates shows up. If you are going to do anything that would push you
past midnight (i.e. into another day) it is best to keep the date and time
together in a single field and separate out the portions you need when you
need them.
 
Back
Top