Date Insert puzzle :: 05/23/76 becomes 12: 12:13

When I attempt to overwrite a date in my date field with a new user selected
valid date eg: 05/23/99
my date field changes to the TIME I updated the field
and does not display my desired date: 05/23/99

Instead it reads: 12:12:03 AM

I am going nuts - even considering converting the field to text. The field
is a SHORT DATE

I can't reproduce this symptom. When I use UPDATE, the new date gets put
into the field.

I'm intrigued. Can you export the table to a new database and send it to me
offline along with the code you're attempting to use to update it? I'll try
and take a look at it by tonight.

Bob Barrows
jason wrote:
> Yeah - its a date/time field - is it possible that if you try to
> overwrite the field with the same date it gets confused and
> differentiates the two by the TIME rather than just displaying the
> date...man, this is such a time waster!
> "Bob Barrows" <> wrote in message
> news:...
>> Something is definitely strange. "05/23/76" becomes "05/26/71"?!?
>> ChangeDate IS a Date/Time field isn't it?
>>
>> Bob
>> jason wrote:
>>> If I run the query in Access it results in: 05/26/71.
>>>
>>> I just cannot figure this out.
>>>
>>> - Jason
>>> "Bob Barrows" <> wrote in message
>>> news:#$...
>>>> Well, that SHOULD work, assuming that you want to set the field to
>>>> 26-May-1976 ...
>>>> You say that this results in 8:00:00 PM ...?
>>>> What if you open Access and use the Query builder to run this
>>>> statement?
>>>>
>>>> Bob Barrows
>>>>
>>>> jason wrote:
>>>>> UPDATE tblListingspriceChanges SET NewPrice =20002, ChangeDate
>>>>> =#05/23/76# WHERE PriceChangeID =28
>>>>>
>>>>> "Bob Barrows" <> wrote in message
>>>>> news:...
>>>>>> What do you get when you do this:
>>>>>> Response.Write UpdateSQL
>>>>>>
>>>>>>
>>>>>>
>>>>>> jason wrote:
>>>>>>> No, that still does not work - no error - but this is what it
>>>>>>> puts in my changeDate field:
>>>>>>>
>>>>>>> 8:00:00 PM
>>>>>>>
>>>>>>> Unbelievable.
>>>>>>> "dlbjr" <> wrote in message
>>>>>>> news:...
>>>>>>>> Try this
>>>>>>>>
>>>>>>>> UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" &
>>>>>>>> NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE
>>>>>>>> PriceChangeID=" & PriceChangeID
>>>>>>>>
>>>>>>>>
>>>>>>>> dlbjr
>>>>>>>>
>>>>>>>> Unambit from meager knowledge of inane others,
>>>>>>>> engender uncharted sagacity.

jason wrote:
> Hi Bob - sure - you can I send you a link to download from your email
> address rather than posting here?
>
> Further as an aside: I just tried again and I noticed something very
> strange:
>
> UPDATE tblListingspriceChanges SET NewPrice =20002, ChangeDate
> =05/23/99 WHERE PriceChangeID =28
>
> ...Produced the following in the field called ChangeDate: 12/30/1899
>
This is because you failed to put the date delimiters around the date. The
statement should be:
.... =#05/23/99# WHERE PriceChangeID =28

Without the delimiters, Jet treats it as a numeric expression. The two
divisions result in a very small fractional number. This where you need to
realize that Jet stores dates as numbers of type Double. The whole number
portion of the number represents the number of days since the seed date:
12/30/1899. The decimal portion represents the time of day, with .5
equalling noon. The result of 5/23/99 is 0.0022. When interpreted as a
date/time, this is equivalent to 12/30/1899 00:05:16. Does this make it any
clearer?

Ok Ray - I got it - sorry If I was slow off the mark - got really tired and
error prone.

One more thing, I would like to bring up. One of my queries extracts the
latest price reductions by date and displays it in browser and it works
great! But,

I notice that if the user posts multiple price reductions on the same day
for the same boat it results in duplication of records. Now, in all
likelihood this would never happen. But, I supsect a solution to my problem
would be to store the TIME after the DATE:

05/23/71 8.002 pm ...which would always keep my records in the query unique.
Is this small/date time and if so what is the correct format inside Access
for the date/time field. Also, will it affect existing queries, updates or
inserts etc if I switch to storing date values like this?

Appreciated
Jason
"Bob Barrows" <> wrote in message
news:...
> jason wrote:
> > Hi Bob - sure - you can I send you a link to download from your email
> > address rather than posting here?
> >
> > Further as an aside: I just tried again and I noticed something very
> > strange:
> >
> > UPDATE tblListingspriceChanges SET NewPrice =20002, ChangeDate
> > =05/23/99 WHERE PriceChangeID =28
> >
> > ...Produced the following in the field called ChangeDate: 12/30/1899
> >
> This is because you failed to put the date delimiters around the date. The
> statement should be:
> ... =#05/23/99# WHERE PriceChangeID =28
>
> Without the delimiters, Jet treats it as a numeric expression. The two
> divisions result in a very small fractional number. This where you need to
> realize that Jet stores dates as numbers of type Double. The whole number
> portion of the number represents the number of days since the seed date:
> 12/30/1899. The decimal portion represents the time of day, with .5
> equalling noon. The result of 5/23/99 is 0.0022. When interpreted as a
> date/time, this is equivalent to 12/30/1899 00:05:16. Does this make it
any
> clearer?
>
> Bob Barrows
>
>

"Bob Barrows" <> wrote in message
news:...
> jason wrote:
> > Ok Ray - I got it - sorry If I was slow off the mark - got really
> > tired and error prone.
>
> Ray?? Well I never ... ;-)
>
> >
> > One more thing, I would like to bring up. One of my queries extracts
> > the latest price reductions by date and displays it in browser and it
> > works great! But,
> >
> > I notice that if the user posts multiple price reductions on the same
> > day for the same boat it results in duplication of records. Now, in
> > all likelihood this would never happen. But, I supsect a solution to
> > my problem would be to store the TIME after the DATE:
> >
> > 05/23/71 8.002 pm ...which would always keep my records in the query
> > unique. Is this small/date time and if so what is the correct format
> > inside Access for the date/time field. Also, will it affect existing
> > queries, updates or inserts etc if I switch to storing date values
> > like this?
> >
> Again, Access date/time fields know nothing about format. Datetimes are
> stored as Doubles. Both time and date are always stored: if you don't
supply
> a time, .0 is stored.
>
> When supplying dates and times, you should use this format: yyyy-mm-dd
> hh:mm:ss. So your date should be supplied as:
> #1971-05-23 20:02:00#
>
> HTH,
> Bob Barrows
>
>

Share This Page

Welcome to The Coding Forums!

Welcome to the Coding Forums, the place to chat about anything related to programming and coding languages.

Please join our friendly community by clicking the button below - it only takes a few seconds and is totally free. You'll be able to ask questions about coding or chat with the community and help others.
Sign up now!