Nasty Quote issue in VBA Queries (1 Viewer)

richardw

Registered User.
Local time
Yesterday, 21:37
Joined
Feb 18, 2016
Messages
48
Hi dear all,

I have this complex query and all is working fine.

Code:
Public Sub refreshTemp(wUser As String, wTeam As String, wMonth As Integer, wYear As Integer, wInactive As Boolean)

Dim textSQL As String
Dim db As DAO.Database, rst As DAO.Recordset, Qdf As DAO.QueryDef
Dim wInit As Double, wBudget As Double, wPrevious As Double
Dim wActual As Double, wRemain As Double, wGapBud As Double, wTotalFTE As Double

Set db = CurrentDb


textSQL = "DELETE * FROM tbl_temp WHERE userName='" & wUser & "'"

CurrentDb.Execute textSQL


If wInactive Then
    Set Qdf = db.QueryDefs("rsel_tblTempInactive")
Else
    Set Qdf = db.QueryDefs("rsel_tblTempActive")
End If
With Qdf
    .Parameters("var1") = wYear
    .Parameters("var2") = wMonth
    .Parameters("var3") = wTeam
End With
Set rst = Qdf.OpenRecordset()


textSQL = "INSERT INTO tbl_temp(userName, idInitiative,initiativeName,initiativeType,teamName,process,rtbctb,initiativeComment,DOMCounterpart,otherCounterpart,idBudget,yearInitialVal,budgetVersion,budgetStatus,budgetComment,idInitialVal,initiativeStatus,initialValExt,budgetValExt,startMonth,endMonth,strStartMonth,strEndMonth,validatorName,validationDate,idActualVal,yearActual,monthActual,previousExt,actualExt,remainExt,totalExt,gapBudgetExt,actualComment,userUpdateActual,dateUpdateActual,userUpdateInitiative,dateUpdateInitiative,initialValInt,budgetValInt,previousInt,actualInt,remainInt,totalInt,gapBudgetInt,initialValOth,budgetValOth,previousOth,actualOth,remainOth,totalOth,gapBudgetOth,initialValTotal,budgetValTotal,previousTotal,actualTotal,remainTotal,gapBudgetTotal,totalTotal) VALUES ("
Do Until rst.EOF
    If rst!typeActual = "EXT" Then
        textSQL = textSQL & "'" & wUser & "','" & rst!idInitiative & "','" & rst!initiativeName & "','" & rst!initiativeType & "','" & rst!teamName & "','" & rst!process & "','" & rst!rtbctb & "','" & rst!initiativeComment & "','" & rst!DOMcounterpart & "','" & rst!otherCounterpart & "','" & rst!idBudget & "','" & rst!yearInitialVal & "','" & rst!budgetVersion & "','" & rst!budgetStatus & "','" & rst!budgetComment & "','" & rst!idInitialVal & "','" & rst!initiativeStatus & "','" & rst!initialVal & "',"
        If rst!budgetVersion <> "NB" Then
            textSQL = textSQL & "'" & rst!initialVal & "',"
            wBudget = rst!initialVal
        Else
            textSQL = textSQL & "'0',"
            wBudget = 0
        End If
        textSQL = textSQL & "'" & rst!startMonth & "','" & rst!endMonth & "','" & rst!strStartMonth & "','" & rst!strEndMonth & "','" & rst!validatorName & "','" & rst!validationDate & "','" & rst!idActualVal & "','" & rst!yearActual & "','" & rst!monthActual & "','" & rst!previousFTE & "','" & rst!actualFTE & "','" & rst!remainFTE & "','" & rst!totalFTE & "','" & rst!gapBudget & "','" & rst!actualComment & "','" & rst!userUpdateActual & "','" & rst!dateUpdateActual & "','" & rst!userUpdateInitiative & "','" & rst!dateUpdateInitiative & "',"
        wInit = rst!initialVal
        wPrevious = rst!previousFTE
        wActual = rst!actualFTE
        wRemain = rst!remainFTE
        wGapBud = rst!gapBudget
        wTotalFTE = rst!totalFTE
        
    ElseIf rst!typeActual = "INT" Then
        textSQL = textSQL & "'" & rst!initialVal & "',"
        If rst!budgetVersion <> "NB" Then
            textSQL = textSQL & "'" & rst!initialVal & "',"
            wBudget = wBudget + rst!initialVal
        Else
            textSQL = textSQL & "'0',"
        End If
        textSQL = textSQL & "'" & rst!previousFTE & "','" & rst!actualFTE & "','" & rst!remainFTE & "','" & rst!totalFTE & "','" & rst!gapBudget & "',"
        wInit = wInit + rst!initialVal
        wPrevious = wPrevious + rst!previousFTE
        wActual = wActual + rst!actualFTE
        wRemain = wRemain + rst!remainFTE
        wGapBud = wGapBud + rst!gapBudget
        wTotalFTE = wTotalFTE + rst!totalFTE
        
    ElseIf rst!typeActual = "OTH" Then
        textSQL = textSQL & "'" & rst!initialVal & "',"
        If rst!budgetVersion <> "NB" Then
            textSQL = textSQL & "'" & rst!initialVal & "',"
            wBudget = wBudget + rst!initialVal
        Else
            textSQL = textSQL & "'0',"
        End If
        textSQL = textSQL & "'" & rst!previousFTE & "','" & rst!actualFTE & "','" & rst!remainFTE & "','" & rst!totalFTE & "','" & rst!gapBudget & "',"
        wInit = wInit + rst!initialVal
        wPrevious = wPrevious + rst!previousFTE
        wActual = wActual + rst!actualFTE
        wRemain = wRemain + rst!remainFTE
        wGapBud = wGapBud + rst!gapBudget
        wTotalFTE = wTotalFTE + rst!totalFTE
        
        textSQL = textSQL & "'" & wInit & "','" & wBudget & "','" & wPrevious & "','" & wActual & "','" & wRemain & "','" & wGapBud & "','" & wTotalFTE & "')"
        
               
        CurrentDb.Execute textSQL
    
        textSQL = "INSERT INTO tbl_temp(userName,idInitiative,initiativeName,initiativeType,teamName,process,rtbctb,initiativeComment,DOMCounterpart,otherCounterpart,idBudget,yearInitialVal,budgetVersion,budgetStatus,budgetComment,idInitialVal,initiativeStatus,initialValExt,budgetValExt,startMonth,endMonth,strStartMonth,strEndMonth,validatorName,validationDate,idActualVal,yearActual,monthActual,previousExt,actualExt,remainExt,totalExt,gapBudgetExt,actualComment,userUpdateActual,dateUpdateActual,userUpdateInitiative,dateUpdateInitiative,initialValInt,budgetValInt,previousInt,actualInt,remainInt,totalInt,gapBudgetInt,initialValOth,budgetValOth,previousOth,actualOth,remainOth,totalOth,gapBudgetOth,initialValTotal,budgetValTotal,previousTotal,actualTotal,remainTotal,gapBudgetTotal,totalTotal) VALUES ("
        
    End If
    
    rst.MoveNext
    
Loop


End Sub

but when I just did some tests of the application, I found that if I have a string record with a single quote it generates an error. I don't know if there is someway to "escape" those quotes.

The generated error message when running the query is attached.

Thank you in advance :)
 

Attachments

  • query error.JPG
    query error.JPG
    17 KB · Views: 172

Ranman256

Well-known member
Local time
Today, 00:37
Joined
Apr 9, 2015
Messages
4,339
if your data has quotes in it you can no longer use the ' character.
you must use the double quote to surround it , so i assign dbl quote to the variable Q.
then my sql look like:

' put 4 here
const Q = """"
sSql = "Select * from table where [name]=" & Q & "bob" & Q
 

richardw

Registered User.
Local time
Yesterday, 21:37
Joined
Feb 18, 2016
Messages
48
if your data has quotes in it you can no longer use the ' character.
you must use the double quote to surround it , so i assign dbl quote to the variable Q.
then my sql look like:

' put 4 here
const Q = """"
sSql = "Select * from table where [name]=" & Q & "bob" & Q

Thank you!

Could you please give me an example applied for this one:

Code:
textSQL = "DELETE * FROM tbl_temp WHERE userName='" & wUser & "'"
 

mdnuts

Registered User.
Local time
Today, 00:37
Joined
May 28, 2014
Messages
128
did you try escaping with replace?
Code:
textSQL = "DELETE * FROM tbl_temp WHERE userName='" & replace(wUser,"'","''") & "'"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:37
Joined
May 7, 2009
Messages
19,169
Create a general purpose function inside a Module, so in the future you will not have any problem:

Public Function SQLQuote(strInput) As String
SQLQuote = Chr(34) & strInput & Chr(34)
End Function

Now on your query just passed any string parameters:

textSQL = "DELETE * FROM tbl_temp WHERE userName=" & SQLQuote(wUser) & ";"
 

Users who are viewing this thread

Top Bottom