By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Syntax error in Insert Into sql statement

P: 86
Hi.

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

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.  
  3.     strSQL = "INSERT INTO usr_problem_list "
  4.     strSQL = strSQL & "(trouble_no, date, user, notes) "
  5.     strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');"
  6.  
  7.     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!
Jan 13 '12 #1

✓ answered by NeoPa

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

EG. [Date].

Share this Question
Share on Google+
11 Replies


P: 86
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!
Jan 13 '12 #2

NeoPa
Expert Mod 15k+
P: 31,492
Use brackets (Actually - better not to use reserved words at all where possible) around it.

EG. [Date].
Jan 13 '12 #3

P: 86
@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 :)
Jan 13 '12 #4

NeoPa
Expert Mod 15k+
P: 31,492
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).
Jan 14 '12 #5

P: 5
Expand|Select|Wrap|Line Numbers
  1. "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
Jan 15 '12 #6

NeoPa
Expert Mod 15k+
P: 31,492
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.
Jan 15 '12 #7

P: 5
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.

Expand|Select|Wrap|Line Numbers
  1. clsobjact.InsertData("insert into Customer(Date)values('" & Format(System.DateTime.Today.Date, "MM/dd/yyyy") & "')")
Jan 16 '12 #8

100+
P: 332
@Monty
Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String 
  2.  
  3.     strSQL = "INSERT INTO usr_problem_list " 
  4.     strSQL = strSQL & "(trouble_no, [date], [user], notes) " 
  5.     strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & txtNotes & "');" 
  6.  
  7.     DoCmd.RunSQL strSQL 
  8.  
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.
Jan 16 '12 #9

NeoPa
Expert Mod 15k+
P: 31,492
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.
Jan 16 '12 #10

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

that was easy!

thanks everyone for the help.
Jan 17 '12 #11

NeoPa
Expert Mod 15k+
P: 31,492
It's an important thing to learn, even if it seems relatively small :-)
Jan 17 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.