update date/time MS Access field via ODBC

[pkocsis]

I have two date/time fields in an Access table. The 'Format' of the date/time fields as I set them up in Access is 'Short Date'

I'm trying to do an sql update to change the value of the date fields.

My test example:

revExecuteSQL ldbid, "update customertable set lastactivitydate1 = :1 and lastactivitydate2 = :2 where customernumber = :3","ttoday1","ttoday2","tcustomernumber"

the variable ttoday1 contains 12/1/2010
the variable ttoday2 contains 12/5/2010

The function returns 1, yet lastactivitydate1 turns out to contain "12/30/1899" and lastactivitydate2 remains unchanged (its value before the sql update was "9/5/2010" and remains that value after the sql update command even though lastactivitydate1 goes from "12/1/2010" to "12/30/1899"!?)

I know google is my friend, but in searching and searching for an answer, it has not been friendly to me today....

can anyone help me out?

THANKS!

[bangkok]

Normally, SQL uses the format YYYY-MM-DD, so try with "2010-12-01" instead of "01/12/2010".

Last advice : make your test with an easier query, like

revExecuteSQL ldbid, "update customertable set lastactivitydate1='2010-12-01' where customernumber ='mycustomer' "

[pkocsis]

Thank you for responding. I have tried your suggestion along with many other permutations of things I've read online. I can't seem to get either date field to change to anything other than 12/30/1899.

I think I'm going to punt on using the date/time data type and simply make it a text field. I know that is crude, but this is only for my own internal database and I've already blown a day on trying to update this blasted data/time field. The funny thing is, I can populate the field when I do an 'insert'....I just can't get it to change to the data I want when I perform an 'update'.

Again, thank you for responding!

[bangkok]

I think I'm going to punt on using the date/time data type and simply make it a text field. I know that is crude, but this is only for my own internal database and I've already blown a day on trying to update this blasted data/time field. The funny thing is, I can populate the field when I do an 'insert'....I just can't get it to change to the data I want when I perform an 'update'.!

Hum... It's working with insert but not update... ? Weird.

-have you tried, to put after revExecuteSQL :

Code: Select all

put the result into theResult
if theResult is not a number then answer theResult

that could display an error message
*********************
-on the MsAccess side : your field is pure "date" or "time stamp" format (YYYY-MM-DD HH:MM:SS) ?
-no strange parameters on the Access db or on the date field ? No auto update (like triggers for SQL) ? Have you tried with a freshly created DB ?

[pkocsis]

Thank you again for responding, I really appreciate it!

-my result was returning '1', obviously for one row updated

-my fields were pure 'date' (actually, set up with Access as date/time with a format of 'short date')

-no strange parameters or auto update or triggers

On your fresh db question, I have to answer 'no'. However, I have since gotten away from the date/time data types and went with pure text. In doing that, a fresh db was created.....and my updates are now working.....so the result is that I don't know what was wrong

It could've been that my db was corrupted....or....my previous attempt was not supplying correct data for the date/time field (although I tried your previous suggestion along with every other date format known to man (this man anyway ).

Either way, it is just an internal db for myself and all is working good enough for my purposes.

Thank you so much for your help....and if I ever have to use Access date/time fields again for a commercial project, I may again solicit your help

[bangkok]

Thank you again for responding, I really appreciate it!
On your fresh db question, I have to answer 'no'. However, I have since gotten away from the date/time data types and went with pure text. In doing that, a fresh db was created.....and my updates are now working.....so the result is that I don't know what was wrong

Well, to go through the text format is really too bad.

You should persevere. What about the "date/time" format ? I don't know MsAcess, but it sounds strange : it's like if you try to put a date only datatype (2010-12-01) into a time stamp format (2010-12-01 01:01:01) it can't work. MsAccess doesn't have a date only datatype ?

You should try to make an update, on your original field, with a timestamp value, to see if it could be the source of the problem.

[bangkok]

i just found something interesting on microsoft website.
http://msdn.microsoft.com/en-us/library ... 85%29.aspx

It's the cause of your problem.

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.

[pkocsis]

That's a nice find, THANK YOU SO MUCH! I did try both {d '2010-12-1'} and #12/1/2010#....but to no avail...

I thought I had tried all permutaions of the above....for example:

set mydatefield = {d ':1'}...

or

set mydatefield = #:1#...

didn't know whether my variable for the place holder should just contain the date....or....whether I was supposed to set my variable = "{d '2010-12-1'}"....(and use set mydatefield = :1)...either way, I failed to get it to work and got frustrated for just a lowly internal db that I needed minimum functionality from.....so....

I shall keep at it to figure it out....

THANK YOU SO MUCH!

[bangkok]

That's a nice find, THANK YOU SO MUCH! I did try both {d '2010-12-1'} and #12/1/2010#....but to no avail...

watch out. you should try '2010-12-01' and not '2010-12-1'


Plus :
'#04-MAY-2004 17:03:34#'

By the way i'm thinking about something.

Since your insert are fine.... what do you get when you do a select ?

it could help you to find out what exactly MsAccess stores in "datetime" field, and how it stores it.

Eventually, I still think that you should try an update with a timestamp value '2010-12-01 01:01:02'.
Just to be sure...