469,301 Members | 2,278 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

Syntax error in Insert Into sql statement

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].

11 3834
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
32,173 Expert Mod 16PB
Use brackets (Actually - better not to use reserved words at all where possible) around it.

EG. [Date].
Jan 13 '12 #3
@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
32,173 Expert Mod 16PB
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
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
32,173 Expert Mod 16PB
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
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
Mariostg
332 100+
@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
32,173 Expert Mod 16PB
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
ok looks like putting [ ] around date fixed the problem!

that was easy!

thanks everyone for the help.
Jan 17 '12 #11
NeoPa
32,173 Expert Mod 16PB
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.

Similar topics

3 posts views Thread by jinhy82 | last post: by
3 posts views Thread by Nathan Sokalski | last post: by
5 posts views Thread by amitbadgi | last post: by
2 posts views Thread by Geoffrey KRETZ | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.