Accommodating an apostrophe in SQL... [modified]

[new_phoenix 0]

I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help. strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _ "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _ "[PRIMARY_KEY] FROM [TBLHYPERION] " & _ "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _ "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _ "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _ "AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _ "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _ "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _ "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _ "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in: "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" :confused::confused::confused:

modified on Wednesday, December 3, 2008 11:31 AM

[Jay Royall]

You need to replace the apostrophy with 2 apostrophies. Best way is probably through a function (although I'm not sure of the exact syntax for VBA) - something like : Public Function MakeSqlSafe(strData) as string Return strData.Replace("'", "''") end function and then pass each one of your inputs through the function, e.g. '" & MakeSqlSafe(UCase(rstInputFile![REGION])) & "' Hope this helps.

[new_phoenix 0]

Thank you, liqz for your assistance. The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub". The code is as follows in VBA: Public Function MakeSqlSafe(strData) As String strNewStrData = Replace(strData, "'", "''") Return End Function strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _ "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _ "[PRIMARY_KEY] FROM [TBLHYPERION] " & _ "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _ "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _ "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _ "AND IsNull([ALT_GROUPING]) " & _ "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _ "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _ "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _ "AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'" Any assistance in resolving this item would be greatly appreciated!!!

[Jay Royall]

Ah, try changing the function to :

Function MakeSqlsafe(strData)
MakeSqlSafe = Replace(strData, "'", "''")
end Function

[new_phoenix 0]

It worked!!! Woo Hoo!!! Thank you liqz, for your assistance in resolving this item!!!

[Wendelius]

Although replacing single apostrophe with double apostrophes will correct your problem, I think you should use parameters instead. Concatenating literal strings leaves you open to sql injections. Also using parameters gives a performance advantage.

The need to optimize rises from a bad design.My articles[^]