Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access VBA - SQL - Encapsulating A Name Containing An Apostrophe

Posted on 2007-08-07
1
Medium Priority
?
399 Views
Last Modified: 2013-11-27
I'm running the following SQL statement and it breaks when rstTimekeeperList!ProfName  = O'Reilly

'sum timekeeper hours
  strSQL = "SELECT tblProfessionalsTime.ProfName, tblProfessionalsTime.TransDate, Sum
                   (tblProfessionalsTime.StmnUnits) AS totalTime" & _
                    " FROM tblProfessionalsTime" & _
                    " GROUP BY tblProfessionalsTime.ProfName, tblProfessionalsTime.TransDate" & _
                    " HAVING (((tblProfessionalsTime.ProfName)='" & rstTimekeeperList!ProfName & "') AND          
                        ((tblProfessionalsTime.TransDate)=#" & rstMinMax!MinOfTransDate + i & "#));"

How could I amend this statement to enable it to parse names which contain apostrophes?

Thanks.

0
Comment
Question by:advlegals
1 Comment
 
LVL 58

Accepted Solution

by:
amit_g earned 2000 total points
ID: 19649114
Use 2 quotes where you need one by using ...

Replace(rstTimekeeperList!ProfName, "'", "''")

i.e.

strSQL = "SELECT tblProfessionalsTime.ProfName, tblProfessionalsTime.TransDate, Sum
                   (tblProfessionalsTime.StmnUnits) AS totalTime" & _
                    " FROM tblProfessionalsTime" & _
                    " GROUP BY tblProfessionalsTime.ProfName, tblProfessionalsTime.TransDate" & _
                    " HAVING (((tblProfessionalsTime.ProfName)='" & Replace(rstTimekeeperList!ProfName, "'", "''") & "') AND          
                        ((tblProfessionalsTime.TransDate)=#" & rstMinMax!MinOfTransDate + i & "#));"
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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