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

"jason" <ja***@catamaranco.com> wrote in message
news:Ou**************@TK2MSFTNGP12.phx.gbl...

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

[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
"Alan" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:OK**************@TK2MSFTNGP11.phx.gbl...

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

"jason" <ja***@catamaranco.com> wrote in message
news:Ou**************@TK2MSFTNGP12.phx.gbl...

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

[jason]

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

8:00:00 PM

Unbelievable.
"dlbjr" <dl***@dontknow.doyou> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...

Try this

UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" & NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE PriceChangeID=" & PriceChangeID
dlbjr

Unambit from meager knowledge of inane others,
engender uncharted sagacity.

[Bob Barrows]

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" <dl***@dontknow.doyou> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...

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]

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

"Bob Barrows" <re*******@yahoo.com> wrote in message
news:OP**************@tk2msftngp13.phx.gbl...

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" <dl***@dontknow.doyou> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...

Try this

UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" &
NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE PriceChangeID="
& PriceChangeID
dlbjr

Unambit from meager knowledge of inane others,
engender uncharted sagacity.

[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 wrote:

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

"Bob Barrows" <re*******@yahoo.com> wrote in message
news:OP**************@tk2msftngp13.phx.gbl...

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" <dl***@dontknow.doyou> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
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]

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

I just cannot figure this out.

- Jason
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:#$**************@TK2MSFTNGP11.phx.gbl...

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" <re*******@yahoo.com> wrote in message
news:OP**************@tk2msftngp13.phx.gbl...

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" <dl***@dontknow.doyou> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
> Try this
>
> UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" &
> NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE
> PriceChangeID=" & PriceChangeID
>
>
> dlbjr
>
> Unambit from meager knowledge of inane others,
> engender uncharted sagacity.

[Bob Barrows]

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" <re*******@yahoo.com> wrote in message
news:#$**************@TK2MSFTNGP11.phx.gbl...

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" <re*******@yahoo.com> wrote in message
news:OP**************@tk2msftngp13.phx.gbl...
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" <dl***@dontknow.doyou> wrote in message
> news:uO**************@TK2MSFTNGP10.phx.gbl...
>> Try this
>>
>> UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" &
>> NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE
>> PriceChangeID=" & PriceChangeID
>>
>>
>> dlbjr
>>
>> Unambit from meager knowledge of inane others,
>> engender uncharted sagacity.

[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 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" <re*******@yahoo.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl...

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" <re*******@yahoo.com> wrote in message
news:#$**************@TK2MSFTNGP11.phx.gbl...
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" <re*******@yahoo.com> wrote in message
> news:OP**************@tk2msftngp13.phx.gbl...
>> 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" <dl***@dontknow.doyou> wrote in message
>>> news:uO**************@TK2MSFTNGP10.phx.gbl...
>>>> 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]

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" <re*******@yahoo.com> wrote in message
news:ug*************@tk2msftngp13.phx.gbl...

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" <re*******@yahoo.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl...

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" <re*******@yahoo.com> wrote in message
news:#$**************@TK2MSFTNGP11.phx.gbl...
> 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" <re*******@yahoo.com> wrote in message
>> news:OP**************@tk2msftngp13.phx.gbl...
>>> 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" <dl***@dontknow.doyou> wrote in message
>>>> news:uO**************@TK2MSFTNGP10.phx.gbl...
>>>>> Try this
>>>>>
>>>>> UpdateSQL = "UPDATE tblListingspriceChanges SET NewPrice =" &
>>>>> NewPrice & ", ChangeDate =#" & ChangeDate & "# WHERE
>>>>> PriceChangeID=" & PriceChangeID
>>>>>
>>>>>
>>>>> dlbjr
>>>>>
>>>>> Unambit from meager knowledge of inane others,
>>>>> engender uncharted sagacity.

[Bob Barrows]

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

[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
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:en**************@TK2MSFTNGP12.phx.gbl...

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

[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" <re*******@yahoo.com> wrote in message
news:uL**************@TK2MSFTNGP11.phx.gbl...

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

[Bob Barrows]

jason wrote:

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
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:#L**************@TK2MSFTNGP12.phx.gbl...

jason wrote:

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