Ms Access date format?

[mattscotney]

Hi all,

I am trying to update my Access database through vb.net using the following sql statement:

Dim myDate As Date = Date.Now

selectString = "INSERT INTO Purchase(ClientID, Date) VALUES (50, '" & mydate & "')"

updateDB(selectString)

The Date field in access is a Date/Time type in short date format.

I know there is nothing wrong with my database connection or updateDB procedure as I can update the clientID and other fields without any hassles.

How do I format the date so that access will accept the sql date query?

Thanks,
Matt Scotney

 

[rosenk]

The standard ToString() conversion for DateTime won't work for Access. You need to convert explicitly, like:

selectString = "INSERT INTO Purchase(ClientID, Date) VALUES (50, '" & mydate[COLOR=red].ToString("d")[/color] & "')"

The "d" tells it to format into the form mm/dd/yyyy, which Access likes.

 

[mattscotney]

Hi again,

I tried your code and I still get the same error.

Thanks,
Matt Scotney

 

[rosenk]

What, exactly, is the error you're getting?

 

[stnkyminky]

Try placing #'s around your date instead of single quote (')

Scott
Programmer Analyst
<{{><

 

[mattscotney]

Nope, #'s around the date didn't work either.

When quering a date eg. SELECT...WHERE...#&quot; &amp; mydate &amp; &quot;#... you have to use the #'s, not when updating / inserting though.

I finally fixed it simply by putting [] around the word date. Sql does not appreciate you using reserved words as field names!

selectString = "INSERT INTO Purchase(ClientID, [Date]) VALUES (50, '" & mydate.ToString & "')"

Hopefully if someone else has this same problem I have solved it for them too.

Thanks for all you help.

 

[stnkyminky]

How do I format the date so that access will accept the sql date query?

I thought you were having problems with the value and not the column name. Yeah, the reserved word problem will drive you crazy....

Scott
Programmer Analyst
<{{><