Problems with running an SQL query from VBA in Access

[markronz]

Hopefully someone will be able to tell me what I am doing wrong here. First here is my table layout:

CONTACTLOG TABLE
FIELD NAME - DATE TYPE - DESCRIPTION
contactLogID - AutoNumber - Priamry Key for comment table
id - Number - foreign key
Date - Date/Time - Date when comment was entered
DebtAgent - Text - Name of the agent
Method - Text - Was it a call/email/letter
reasonForCall - Text - Summary of reason for calling
txtLog - Memo - comments

From my VBA code I am trying to execute this SQL insert command:

INSERT INTO contactLog (id, Date, DebtAgent, Method, reasonForCall, txtLog) VALUES (53, "1/18/2008 9:54:21 PM", "Mark", "Letter", "test", "test")

When it tries to run this command, I get this error:

Run-time error '3134':
Syntax error in INSERT INTO statement.

Does anyone have any ideas what I am messing up here? Is it the time or something? It seems like it should be right. I checked out some existing values already in the table and they look like this:
1/17/2008 11:50:30 AM

Or do you think it's something to do with the AutoNumber for the contactLogID field?

I'm at a loss! Help please!

Thanks
-Mark

 

[markronz]

I got it to work myself. In addition to having the number in the correct format of YYYY-MM-DD, I was also using the reserved word "Date" which it didn't like. Once I made that change and then changed the field name to something else, it worked fine.

Thanks anyway!

 

[JerryKlmns]

markronz

A yyyy-mm-dd is an unambiguous ANSI separeted date format.
VBA expects dates to follow mm/dd/yy format but when regional settings are different you get wrong results. So stick with it.

Names of fields containing spaces and/or special characters or reserved words of MSAccess, have to be enclosed in brackets ie [my spaced field name]. That way, everything works. But it is strongly advised to avoid such field names. So instead of field name Date use TheDate or whatever. Users should not see tables directly but only through forms or queries, so naming table fields is only for the developers the way it helps them.

 

[randy700]

INSERT INTO contactLog (id, Date, DebtAgent, Method, reasonForCall, txtLog) VALUES (53, [COLOR=red][b]#[/b][/color]1/18/2008 9:54:21 PM[COLOR=red][b]#[/b][/color], "Mark", "Letter", "test", "test")

Randy

 

[GhostWolf]

Although "Date" is a reserved word, you can still get away with addressing it as a field name by enclosing it in brackets:

INSERT INTO contactLog (id, [COLOR=red][[/color]Date[COLOR=red]][/color], DebtAgent, Method, reasonForCall, txtLog) VALUES (53, #1/18/2008 9:54:21 PM#, "Mark", "Letter", "test", "test")