Syntax Error when using Apostrophe or comma

[violentjay25]

I am having an issue when I use Apostrophe's or commas. If I do not use this punctuation it works fine. Please Advise.


Private Sub cmdAdd_Click()
DoCmd.RunSQL "Insert into tbl_Projects (ProjectName, SystemsImpacted, SPRNum,ReleaseDate, Status, CSIPM, BPM, Implemented, StakeHolder, IBR1, IBR2, IBR3, Objective, SMERequirments, Phase) " & _
"Values ('" & Me.txtProjectName & "', '" & _
Me.TxtSystemsImpacted & "', '" & _
Me.txtSPR & "', '" & _
Me.txtReleaseDate & "', '" & _
Me.CboStatus & "', '" & _
Me.txtCSI & "', '" & _
Me.txtBPM & "', '" & _
Me.cboImplemented & "', '" & _
Me.cboStakeholder & "', '" & _
Me.cboIBR1 & "', '" & _
Me.cboIBR2 & "', '" & _
Me.cboIBR3 & "', '" & _
Me.txtObjective & "', '" & _
Me.txtSMERequirements & "', '" & _
Me.cboPhase & "')"

 

[pdx_man]

Take a look at the SQL after the string is created and see if you have any issues there. Copy it out to a query and see if it works:

Private Sub cmdAdd_Click()
Dim MyVar AS String

MyVar = "Insert into tbl_Projects (ProjectName, SystemsImpacted, SPRNum,ReleaseDate, Status, CSIPM, BPM, Implemented, StakeHolder, IBR1, IBR2, IBR3, Objective, SMERequirments, Phase) " & _
"Values ('" & Me.txtProjectName & "', '" & _
Me.TxtSystemsImpacted & "', '" & _
Me.txtSPR & "', '" & _
Me.txtReleaseDate & "', '" & _
Me.CboStatus & "', '" & _
Me.txtCSI & "', '" & _
Me.txtBPM & "', '" & _
Me.cboImplemented & "', '" & _
Me.cboStakeholder & "', '" & _
Me.cboIBR1 & "', '" & _
Me.cboIBR2 & "', '" & _
Me.cboIBR3 & "', '" & _
Me.txtObjective & "', '" & _
Me.txtSMERequirements & "', '" & _
Me.cboPhase & "')"

Debug.Print MyVar
DoCmd.RunSQL MyVar

Initially, I would say that you have a Field that is not a text data type and it is getting confused.

 

[WayneRyan]

vj,

Lifted from another thread. Need appropriate punctuation around your
data values.

String: sql = "[Field] = '" & Me.SomeField & "'" <-- Single-Quote
Date: sql = "[Field] = #" & Me.SomeField & "#" <-- Pound Sign
Number: sql = "[Field] = " & Me.SomeField <-- No delimiter

Wayne