Syntax error

[AJ]

Anyone see a syntax error with this?

DoCmd.RunSQL "Insert into tableA ( LName, Fname, IDNumber" & " '_)" & _
"values ( '" & rs.Fields(0) & "', '" & rs.Fields(1) &
"', '" & rs.Fields(57) & "')"

 

[Dirk Goldgar]

Anyone see a syntax error with this?

DoCmd.RunSQL "Insert into tableA ( LName, Fname, IDNumber" & " '_)" & _
"values ( '" & rs.Fields(0) & "', '" & rs.Fields(1) &
"', '" & rs.Fields(57) & "')"

Yes. What is this:

& " '_)"

?

Maybe you meant to write this:

DoCmd.RunSQL _
"Insert into tableA ( LName, Fname, IDNumber ) "& _
"values ( '" & rs.Fields(0) & "', '" & rs.Fields(1) & _
"', '" & rs.Fields(57) & "')"

 

[Douglas J. Steele]

In addition to what Dirk pointed out, be aware that should either the first
name or last name contain and apostrophe (such as D'Arcy or O'Reilly), even
the corrected code will fail.

You need to ensure that any apostrophes are doubled in the SQL:

DoCmd.RunSQL _
"Insert into tableA ( LName, Fname, IDNumber ) "& _
"values ( '" & Replace(rs.Fields(0), "'", "''") & "', '" & _
Replace(rs.Fields(1), "'", "''") & _
"', '" & rs.Fields(57) & "')"

Exagerated for clarity, that's

DoCmd.RunSQL _
"Insert into tableA ( LName, Fname, IDNumber ) "& _
"values ( ' " & Replace(rs.Fields(0), " ' ", " ' ' ") & " ', '" & _
Replace(rs.Fields(1), " ' ", " ' ' ") & _
" ', ' " & rs.Fields(57) & " ' )"

Note, too, that that code assumes IDNumber is a text field, not a numeric
one.

If it's numeric, lose the single quotes around Fields(57):

DoCmd.RunSQL _
"Insert into tableA ( LName, Fname, IDNumber ) "& _
"values ( ' " & Replace(rs.Fields(0), " ' ", " ' ' ") & " ', '" & _
Replace(rs.Fields(1), " ' ", " ' ' ") & _
" ', " & rs.Fields(57) & " )"