Inserting Date into Access

[Hatch]

I'm having a problem inserting the date into access.
My access field is formatted as mm/dd/yyyy.

This is what I have in my asp page.

Dim dtToday
dtToday = Date()

objRS.AddNew
objRS("Date") = dtToday
objRS.Update

Then I get this error message.

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Anybody know what i'm doing wrong.
Thanks

[Morgoth]

I don't see a table anywhere.

<edit>
And I can't remember, but maybe you should rename your field name of "Date" to something like UserDate or PollDate or D_Date.

I think I had some type of problem like that before when I did that, Might be a totaly differnt story.
</edit>

[whammy]

Can we see the insert statement? That seems to be the problem yet you didn't include that code.

[BrainJar]

I think you need to use square brackets for the column name:

objRS("[Date]") = dtToday

because "Date" is a reserved word in Access SQL.

[ReyN]

hi, try . . .

Dim dtToday
dtToday = Date

objRS.AddNew
objRS("Date") = dtToday
objRS.Update

[QuackHead]

Originally posted by ReyN
hi, try . . .

Dim dtToday
dtToday = Date

objRS.AddNew
objRS("Date") = dtToday
objRS.Update

This should work - Date is a function, but never has any parameters in it, therefore, it doesn't need to have the () attached at the end...

Also, what BrainJar said could also be true, try changing the column name to "theDate" or "CreationDate" or something...

~Quack

[Morgoth]

L0L.

This is basically all I had, when I put in the date into my db.
SQL = "INSERT INTO table (tabledate) VALUES (" & Now() & ")"

I don't see a problem with it, nor would you. But at odd time, it did the little screw up I had told you.

I don't know why it happend. all it was, was a mistake for the day and month. And I fixed it by:
Month(Now()) & "/" & Day(Now()) & "/" & Year(Now()) & " " & FormatDateTime(Now(), vbLongTime)

And Whammy's idea:
default value.

I have my db's all set up to do it all 3 ways. So, if anyone of them messes up, and they are do the problem anyways, I'll post another topic.

Whammy, if it's all right with you and everyone else, this topic can be locked. I think the problem is sovled...
For Now()

[ReyN]

if all you need is a date, use Date. if you need date and time, use Now.

NOTE: Both do not use parentheses.

[Morgoth]

I am a little edgy on what "parentheses" is, but I think I'll be fine for the mean part.