Handling an apostrophe in user input search

Posted on 2009-04-27
Last Modified: 2013-11-27
I have created this function to compare user input to a set of addresses in one of my tables, but I get an error when the user uses an  apostrophe.  How do I handle that?
varINPUT = varDATA

If IsNull(varINPUT) = True Or IsNumeric(varINPUT) = True Then



Set CNT = CurrentProject.Connection

strSQL = "SELECT vGIS_Data_Address.ADDR FROM "

strSQL = strSQL & "vGIS_Data_Address WHERE (("

strSQL = strSQL & "(vGIS_Data_Address.ADDR)=" & "'" & varINPUT & "'" & "));"

RST.Open strSQL, CNT, adOpenKeyset, adLockOptimistic

intCOUNT = RST.RecordCount



Set CNT = Nothing

If intCOUNT <> 1 Then




End If

End If

Open in new window

Question by:AaronGreene1906
    LVL 25

    Expert Comment

    option 1.)  replace all ' in user input with two '' in database, when getting from database to user input, replace all '' double apostrophes with ' single

    option 2.) replace all user input ' apostrophes with the ` (char below the tilde ~)

    LVL 46

    Accepted Solution

    Hi Aaron,

    You can replace the apostrophes with chr(34)

    Good Luck!

    LVL 25

    Expert Comment

    Using ADO.NET command objects handles this and SQL injection attacks for you.  Maybe the plain old ADO command object does the same--I am not sure.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now