I'm trying to use a 'dynamic' variable in Values list instead of static value.. but I can't get it to work. Can anyone help?

db.Execute "INSERT INTO [tbl] (name) VALUES (rst.Fields![name].Value);"

You mean like the following?

db.Execute "INSERT INTO [tbl] (name) VALUES (" & rst.Fields![name].Value & ");"

Results in error..

MS Access Run-time error '3075': Syntax error (missing operator) in query expression '440A01'.

I'm not sure what operator is needed?

The line has to be in a module, not in the query itself.

Solution was...

strSQL="INSERT INTO mytable (fielda,fieldb,fieldc) VALUES ('" & a & "','" & b & "','" & c & "')"

* note the single quotes around the variables

When Variable has ' or " in text field
I get Syntax error (missing Operator). Can anyone help?

There are other characters that cause problems too sometimes, although I don't remember which ones offhand, but you just double them also.

str = 'Field string to insert
str = replace(str,"'","''")
str = replace(str,chr(34),chr(34) & chr(34))

' Or in one line, given str is the string to insert:
... VALUES ('" replace(replace(str,"'","''"),chr(34), chr(34) & chr(34)) & "')"

Of course if it's done in a module or code, you could open recordsets for the data (origin and destination) and not have to worry about the special characters.


[Edited by JLRodgers on 18-07-2002 at 12:45 PM GMT]

Need help...I can't seem to get Replace (syntax) to work. I want SQL to insert fields to table (and ignore/replace any special characters like the ' ).

db.Execute "INSERT INTO [tbl Exceptions Table] (SWLIN, GCMA, HULL, DEFICIENCY, CORR_BY, CORR_PER, STATUS, DESCRIPT) VALUES " _
& "('" & rst.Fields![SWLIN].Value & "', '" & rst.Fields![GCMA].Value & "', '" & rst.Fields![HULL].Value & "'," _
& " '" & rst.Fields![DEFICIENCY].Value & "', '" & rst.Fields![CORR_BY].Value & "', '" & rst.Fields![CORR_PER].Value & "', " _
& " '" & rst.Fields![STATUS].Value & "', '" & Replace (rst.Fields![DESCRIPT].Value, " '", "''"));"

[Edited by admin on 19-07-2002 at 04:36 PM GMT]