Hi dear all,
I have this complex query and all is working fine.
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
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