Quotes in string impeding SQL behavior

[LARiot]

In a text box in a form using double quotes throws off the SQL statement.

Originally string variables were enveloped in single quotes. Since this created problem they were replaced with double quotes.

Is there a simple method to fix this?

 

[Remou]

What is the SQL statement? For example, this works for me:

[tt]strSQL = "Select * From tblTable where TextText='" & Me.TextText & "'"[/tt]

Where TextText="a"

 

[PHV]

A safer way:

strSQL = "SELECT * FROM tblTable WHERE TextText='" & Replace(Me!TextText, "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

PHV,
LARiot mentions double quotes ... ?

 

[PHV]

Yes, he mentionned also that single quotes created problems.
I've posted a suggestion that shouldn't create problem ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya LARiot . . .

. . . and your SQL is? . . .

See Ya! . . . . . .

 

[LARiot]

Sometimes the users will input single quotes, sometimes double.

This error message pops up:

Syntax error (missing operator) in query expression '"Why was loaner provided for 14 days "double" ''single''"'.

Here's the entire code for this function:

Private Sub cmdUpdate_Click()
    Dim sSQL, sCondition As String
    Dim vROComment As Variant

    Call CalculateRanking

    If Nz(cboDealerCode) = "" Or Nz(Me.txtASM) = "" Or Nz(Me.fraAssessment) = "" Or Nz(Me.txtRO) = "" Then
        MsgBox "Dealer Code, ASM, RO and Assessment needed to save record.", vbCritical
        Exit Sub
    End If
    
    sCondition = "DealerCode = '" & cboDealerCode & "' AND ASMName = '" & Me.txtASM & "' AND After = " & Me.fraAssessment & " AND RONo = '" & Me.txtRO & "'"
    If DCount("*", "tblROMeasurement", sCondition) = 0 Then
        MsgBox "Record doesn't exist.  Try Creating it before updating it."
    End If
    
    vROComment = """" & Replace(Me.txtROComment, "'", "''") & """"
    DoCmd.SetWarnings False
    sSQL = "UPDATE tblROMeasurement SET " & _
           "SpinNo = '" & Me.txtSpinNo & "', " & _
           "TypeOfSystem = '" & Me.cboTypeOfSystem & "', " & _
           "InvoiceType = '" & Me.cboInvoiceType & "', " & _
           "InvoiceAmount = '" & Me.txtInvoiceAmount & "', " & _
           "Tenure = '" & Me.cboTenure & "', " & _
           "CustNameAddr = " & Me.chkCustNameAddr & ", " & _
           "CustPhoneNo = " & Me.chkCustPhoneNo & ", " & _
           "VehicleYearModel = " & Me.chkVehicleYearModel & ", " & _
           "VIN = " & Me.chkVIN & ", " & _
           "MileageIn = " & Me.chkMileageIn & ", " & _
           "MileageOut = " & Me.chkMileageOut & ", " & _
           "DateIn = " & Me.chkDateIn & ", " & _
           "DateOut = " & Me.chkDateIn & ", " & _
           "LicensePlateNo = " & Me.chkLicensePlateNo & ", " & _
           "ChargeSummary = " & Me.chkChargeSummary & ", " & _
           "LineItemChrg = " & Me.chkLineItemChrg & ", "
    sSQL = sSQL & _
           "CustStatementConcern = " & Me.chkCustStatementConcern & ", " & _
           "CauseDesc = " & Me.chkCauseDesc & ", " & _
           "RemedyIdentified = " & Me.chkRemedyIdentified & ", " & _
           "PartsItemized = " & Me.chkPartsItemized & ", " & _
           "ProcDescDiagnRead = " & Me.chkProcDescDiagnRead & ", " & _
           "CannotDuplicateDesc = " & Me.chkCannotDuplicateDesc & ", " & _
           "NoRepair = " & Me.chkNoRepair & ", " & _
           "NoUncommonAcronyms = " & Me.chkNoUncommonAcronyms & ", "
    sSQL = sSQL & _
           "RegionContacted = " & Me.chkRegionContacted & ", " & _
           "GoodwillNotation = " & Me.chkGoodwillNotation & ", " & _
           "Points = " & Me.txtScore & ", " & _
           "ROComment = " & vROComment & " " & _
           "WHERE DealerCode = '" & Me.cboDealerCode & "' " & _
             "AND After = " & Me.fraAssessment & " " & _
             "AND RONo = '" & Me.txtRO & "' " & _
             "AND ASMName = '" & Me.txtASM & "'"
    DoCmd.RunSQL sSQL

End Sub

 

[PHV]

Sometimes the users will input single quotes, sometimes double.
so, you definitively have to use use my suggestion ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LARiot]

I did PH, but I'm still gettin':

Syntax error (missing operator) in query expression 'Why was loaner provided for 14 days ''single'' "double"'.

The problem is that the quotes close the string.

Meaning 'This 'statement'' is read as 'This ', statement and an undefined variable and then ''.

 

[PHV]

I did PH
You did WHAT, WHERE ?
No trace of my suggestion in your posted code ...

 

[LARiot]

vROComment = """" & Replace(Me.txtROComment, "'", "''") & """"

(about the 17th line of code as parsed here)

I took out the leading and trailing double quotes to no avail.

 

[PHV]

Sorry, didn't see it :~/
My suggestion was this:

...
  "Points = " & Me.txtScore & ", " & _
  "ROComment = '" & Replace(Me.txtROComment, "'", "''") & "' " & _
  "WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LARiot]

Haha! It worked!

You sir are a genius.


BTW Hi AceMan, I'm doing fine (now ) You?

 

[TheAceMan1]

LARiot . . .

AceMan doing well . . . glad your issue is resolved!

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .

 

[LARiot]

Yeah, me too. Thanks.