Syntax error in Insert Into sql statement

[Jeffrey Tan]

Hi.

I am trying to execute the following SQL statement, but I'm getting a syntax error

    Dim strSQL As String
 
    strSQL = "INSERT INTO usr_problem_list "
    strSQL = strSQL & "(trouble_no, date, user, notes) "
    strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');"
 
    DoCmd.RunSQL strSQL

trouble_no is a number field
date is a date/time field
user is a text field
notes is a memo field

thanks!

[Jeffrey Tan]

ok after playing around with it, the culprit seems to be the "date" bc it's a reserved word?

is there any way around this?

thanks!

[NeoPa]

Use brackets (Actually - better not to use reserved words at all where possible) around it.

EG. [Date].

[Jeffrey Tan]

@NeoPa:

thanks for the reply. I'll have to test that out when im at work.

I tried doing table.column, but no luck.

The only other way would be to rename it to something else, but not sure where else the table is used in VBA. so it might create problems, which I definitely do not want that to happen :)

[NeoPa]

Normally Table.Date would work reliably, I'm not so sure for the syntax here though. The parentheses enclose a field list specifically. IE. A list of field names rather than references to fields.

The brackets [] are also useful for specifying names which include spaces, as SQL typically treats spaces (and any white-space) as separators (but not when they're within brackets of course).

[Monty sing]

"INSERT INTO usr_problem_list(trouble_no, date, user, notes)VALUES(" & Text30.Value & ",'" & Format(System.DateTime.Today.Date, "MM/dd/yyyy") & "', '" & user_name & "', '" & txtNotes & "')"

The Sql Server save Date in above format so use this and he datatype in DB should be Datetime. Try above query

[NeoPa]

I think you misunderstand Monty. The problem is not with the use of the Date() function (and certainly not with Now() - however inappropriate this *might* be). The problem is with the use of the word 'Date' to name the field in the record.

[Monty sing]

Dear NeoPa i Checked the Date Keyword in my Sql server DB but it's not showing anytype of error as you said Date is reserved keyword. I m using the below query for Checking.

clsobjact.InsertData("insert into Customer(Date)values('" & Format(System.DateTime.Today.Date, "MM/dd/yyyy") & "')")

[Mariostg]

@Monty

    Dim strSQL As String 
 
    strSQL = "INSERT INTO usr_problem_list " 
    strSQL = strSQL & "(trouble_no, [date], [user], notes) " 
    strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');" 
 
    DoCmd.RunSQL strSQL 
 

Look at line 4, field date and user between square brackets. As NeoPa mentionned, date is a field name to avoid. As far as user go, it is a DAO object. I am a little bit surprised, but in my case, it would not work unless I used square brackets.

[NeoPa]

Monty, you seem to be under the impression that we are in the SQL Server forum. This forum is for Access / VBA.

In Access, there is a function called Date() which is not only available within VBA, but also within the Jet SQL engine. Hence, a reference to Date within Jet SQL (The native SQL engine of MS Access), without the clarification of the brackets around it (as in [Date]) would be likely to be misconstrued. On the other hand of course, with the brackets the problem disappears. A fairly convincing confirmation that this identifies the problem correctly I would think.

PS. Please remember in future to post all your code in [ CODE ] tags as the site rules require.

[Jeffrey Tan]

ok looks like putting [ ] around date fixed the problem!

that was easy!

thanks everyone for the help.

[NeoPa]

It's an important thing to learn, even if it seems relatively small :-)