Syntax error in query expression

  • Thread starter Thread starter Maciej Paras
  • Start date Start date
M

Maciej Paras

Hello!
I get this error: Run-time error 3075 when I'm trying to update a table...
This is the written code:

Private Sub Command34_Click()

Dim Supplier As String

Supplier = Forms![Form1]![Field1]

Dim TheSQLQuery
TheSQLQuery = "INSERT INTO [Table1]([Supplier Address], [Supplier Name])
VALUES(' My address', "&[Supplier]&")"
DoCmd.RunSQL TheSQLQuery

End Sub

And everything work fine if Supplier contains only one word, e.g. FirmX. But
when the field Supplier contains more than 1 word, e.g. Firm X Co. , Access
returns this error message: "Syntax error (missing operator) in query
expression 'Firm X Co.' ", and doesn't update the table...
Are there wrong defined variables?
 
Just requires quotes round Supplier as follows:

Dim TheSQLQuery
TheSQLQuery = "INSERT INTO [Table1]([Supplier Address],
[Supplier Name])
VALUES('My address', '" & [Supplier] & "')"
DoCmd.RunSQL TheSQLQuery
 
Safer to use:

TheSQLQuery = "INSERT INTO [Table1] ([Supplier Address], _
[Supplier Name]) VALUES(' My address', " & _
Chr$(34) & [Supplier] & Chr$(34) & ")"
 
Back
Top