Click here to Skip to main content
14,660,392 members
Rate this:
Please Sign up or sign in to vote.
I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help.

<br />strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _<br /> "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _<br /> "[PRIMARY_KEY] FROM [TBLHYPERION] " & _<br /> "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _<br /> "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _<br /> "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _<br /> "AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _<br /> "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _<br /> "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _<br /> "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _<br /> "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"<br />

What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in:

<br />"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" <br />

[confused][confused][confused]

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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.

   



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100