Runtime Error 3134 Syntax Error in INSERT INTO statement 2

[daveinchicago]

I can't for the life of me figure this out. The syntax looks good to me. Any help would be appreciated.

thanks

CurrentDb.Execute "Insert Into RulesNotCrD(AccountName, Note) Values ('" & Me.cboAcName & "','" & Me.Text18 & "')

 

[PHV]

What about this ?

CurrentDb.Execute "INSERT INTO RulesNotCrD(AccountName,Note) VALUES ('" & Replace(Me.cboAcName, "'", "''") & "','" & Replace(Me.Text18, "'", "''") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daveinchicago]

Thank you!!

Just tried. Same exact error...

wierd...

 

[daveinchicago]

However, this works...

CurrentDb.Execute "Insert Into RulesNotCrD(AccountName) Values ('" & Me.cboAcName.Value & "')

 

[daveinchicago]

It was the use of the column name 'Note'. I changed the column name to 'RuleNote' and it works great!

thank you!!

 

[TheAceMan1]

How are ya daveinchicago . . .

The SQL of your post origination is right on, only I'd put a space where shown ...

[blue]    RulesNotCrD[COLOR=black red] [/color](AccountName, Note)[/blue]

... for easy reading. Otherwise at 1st glance [blue]RulesNotCrD(AccountName, Note)[/blue] looks like a function ... although it should still work.

In any case I couldn't find anything wrong either. So I duplicated the table & fields and setup an [blue]append[/blue] query in query design view to simulate the sql. To my surprise when I went to [blue]SQL View[/blue], I found that brackets [] embraced the [blue]Note[/blue] field:

[blue]INSERT INTO RulesNotCrD ( AccountName, [COLOR=black Yellow][[/color]Note[COLOR=black Yellow]][/color] )[/blue]

Removing the brackets I received the same error you quoted earlier.

So I tried the same in your VBA SQL and ... Voila! ... works as expected:

[blue]   SQL = "Insert Into RulesNotCrD (AccountName, [COLOR=black Yellow][[/color]Note[COLOR=black Yellow]][/color]) " & _
         "Values ('" & Me.cboAcName & "','" & Me.Text18 & "');"[/blue]

Why this happens I can't say yet. One guess is that maybe [blue]Note[/blue] has been promoted to a reserved word in 2003 or greater.

In any event add the brackets [] an see if it makes the difference.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Ace you are correct 'Note' is a reserved word in 2002-2003. For those interested here is a list of reserved words from MS

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Allen Browne has another great source of Problem names and reserved words in Access.

Duane
Hook'D on Access
MS Access MVP