Syntax error in append SQL string

Zooropa

Registered User.
Local time
Today, 08:37
Joined
Sep 14, 2004
Messages
13
Code:
sqlAdd = "Insert into [contacts] ([CONT_TYPE], [INC_OUT], [FACILITATOR], [CONT_PERSON], [PREM_ID], [CONT_DTTM], [NOTES], [OUTCOME] ) " & _
         "VALUES ('" & v_cont_type & "', '" & v_inc_out & "', '" & v_facilitator & "', '" & v_cont_person & "', '" & v_prem_ID & "', #" & v_cont_dttm & "#, '" & v_cont_notes & "', '" & v_cont_outcome & "' ) "

DoCmd.RunSQL sqlAdd

All the variables in the "value" section of the SQL are coming from the values in text/combo boxes on a form.

When "v_cont_notes" has an apostophe in it, for example, "Pharmacist said there wasn't a problem", then the code keels over with a syntax error.

Can anyone think of a work-around?
 
An Append qeury does not work like that, it will append a record from one table to another. You need to use some code similar to below


Dim myRec as Dao.Recordset

Set myRec=currentdb.openrecordset("contacts")

with myRec
.addnew
.Fields("Con_Type")=v_cont_type
.....
......
.....
.update

End with
 
KeithG said:
An Append qeury does not work like that, it will append a record from one table to another. You need to use some code similar to below


Dim myRec as Dao.Recordset

Set myRec=currentdb.openrecordset("contacts")

with myRec
.addnew
.Fields("Con_Type")=v_cont_type
.....
......
.....
.update

End with

I got it, it works with:

Code:
v_cont_notes = Chr(34) & v_cont_notes & Chr(34)

sqlAdd = "Insert into [contacts] ([CONT_TYPE], [INC_OUT], [FACILITATOR], [CONT_PERSON], [PREM_ID], [CONT_DTTM], [NOTES], [OUTCOME] ) " & _
         "VALUES ('" & v_cont_type & "', '" & v_inc_out & "', '" & v_facilitator & "', '" & v_cont_person & "', '" & v_prem_ID & "', #" & v_cont_dttm & "#, " & v_cont_notes & ", '" & v_cont_outcome & "' ) "

DoCmd.RunSQL sqlAdd
 
Are you sure it works?
If your field is defined as text then I would expect you to still need the single quotes in your statement?
 
Sorry... wasn't paying attention to this bit...

Code:
v_cont_notes = Chr(34) & v_cont_notes & Chr(34)
 

Users who are viewing this thread

Back
Top Bottom