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

[jason]

I'm tearing out my hair here: ACCESS 2000:

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

Here is my sql statement:

ChangeDate = request.form.item("ChangeDate") '// 05/23/76

UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" & NewPrice & ",
ChangeDate =" & ChangeDate & " WHERE PriceChangeID =" & PriceChangeID
'Response.end cnn.Execute(UpdateSQL)

Here is the result:

UPDATE tblListingspriceChanges SET NewPrice =20002, ChangeDate =05/23/76
WHERE PriceChangeID =28

Here is what ChangeDate Field looks like in Access:

12:04:07 AM

- I have even tried Cdate(ChangeDate) but this appears to make no
difference.

- Jason

 

[Alan]

Can't say if this is the cause for sure - I haven't used Access in ages, but
your dates should be delimited with #'s. Try that to see if it makes a
difference.

Alan

 

[dlbjr]

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]

Man, I don't know: I tried doing this:
ChangeDate="#" & ChangeDate & "#"
....but then this generates a syntax error. There has got to be simple way to
insert a date into the date field of Access....

- Jason

 

[jason]

No, that still does not work - no error - but this is what it puts in my
changeDate field:

8:00:00 PM

Unbelievable.

 

[Bob Barrows]

What do you get when you do this:
Response.Write UpdateSQL

 

[jason]

UPDATE tblListingspriceChanges SET NewPrice =20002, ChangeDate =#05/23/76#
WHERE PriceChangeID =28

 

[Bob Barrows]

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]

If I run the query in Access it results in: 05/26/71.

I just cannot figure this out.

- Jason

 

[Bob Barrows]

Something is definitely strange. "05/23/76" becomes "05/26/71"?!?
ChangeDate IS a Date/Time field isn't it?

 

[Bob Barrows]

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]

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

But

When I click in the field it suddenly changes to time: 12:03:10 AM

AND

When I go to another field it reverts back to: 12/30/1899

It must have something to do with the format of the Date/Time field which is
currently set to Short Date. I have fooled around with changing options but
this does not make a difference.

 

[Bob Barrows]

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

 

[jason]

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

 

[jason]

Ok - how would append the time to the end of my user defined date in Access.
Would I need to do string manipulation on:
Now() which produces: 9/2/2003 12:38:31 PM

to get: 12:38:31

And add it to my user defined date eg: 05/23/71 (I am following the sequence
Access gives me: month, day, year)

To get:

#05/23/71 12:38:31#

?

Thanks
jason

 

[Bob Barrows]

Ok - how would append the time to the end of my user defined date in
Access. Would I need to do string manipulation on:
Now() which produces: 9/2/2003 12:38:31 PM

to get: 12:38:31

And add it to my user defined date eg: 05/23/71 (I am following the
sequence Access gives me: month, day, year)

To get:

#05/23/71 12:38:31#

?

Sure! That would work.Although, instead of string manipulation, you can use
FormatDateTime to extract the Short Time:

dim d
dim d d= cdate("5/23/71")
d= d + cdate(FormatDateTime(now,vbShortTime))
Response.Write d

HTH,
Bob Barrows

 

[jason]

Works like a dream thanks - amazing what one can learn in the matter of 48
hours!


Jason