Results 1 to 7 of 7
  1. #1
    asmith78 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2011
    Posts
    15

    SQL statement: Help with Apostrophe's in Employee's names


    So I know my issue is only occuring when an employee has an apostrophe in their name. How do I correct this? I've googled for almost an hour and nothing I find seems to workk, or I am just not entering it correctly. PLEASE Help! Here is the SQL statement and I have c = rst1!Empname from the strSQL1 statement in my code. Let me know if you need to see ALL coding. MUCH THANKS

    strSQL2 = "UPDATE tblAudit set tblAudit.audAuditorDateT1 = Date(), tblAudit.audTimeStampT1 = Now(), tblaudit.audAuditorT1 = FOSUserName(), " & _
    "tblaudit.audGrpName = '" & a & "'" & ", tblaudit.audEmpSSN = '" & b & "'" & ", tblaudit.audEmpName = '" & c & "'" & ", tblaudit.audGrpNumber = '" & d & "'" & ", " & _
    "tblaudit.audACPrem = " & e & "" & ", tblaudit.audCIPrem = " & f & "" & ", tblaudit.audHIPrem = " & G & "" & ", tblaudit.audDIPrem = " & H & "" & ", " & _
    "tblaudit.audULPrem = " & J & "" & ", tblaudit.audCAPrem = " & K & "" & ", tblaudit.audTLPrem = " & L & "" & ", tblaudit.audWLPrem = " & M & "" & ", " & _
    "tblaudit.audDNPrem = " & N & "" & ", tblaudit.audTotPrem = " & p & "" & ", tblaudit.audBillCode = '" & q & "'" & ", tblaudit.audCertificate = '" & r & "'" & " " & _
    "WHERE (((tblAudit.audAuditID)='" & AID & "'" & "));"

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    when sql statements are written like what you're doing here, the printout would look like this:

    Code:
    tblaudit.audEmpName = 'value'
    so when there's an apostrophe in the name, the printout would look like:


    Code:
    tblaudit.audEmpName = 'val'ue'
    which obviously is in error (as you can see by the mix-up in the coloring scheme for the 'SQL' tag that I put that statement in! try this first:


    Code:
    tblaudit.audEmpName = " & """" & c & """" & ", etc...
    that will replace those single quotes with doubles. I (think) that'll solve the problem, because singles inside doubles are ignored in terms of their intended meaning to visual basic. As in - the single quotes become string "characters" instead of string indicators.

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I did just realize that this is in the SQL SERVER section. although I intended my response to be for vb in general, it would probably work with sql server too. I would guess anyway.

  4. #4
    asmith78 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2011
    Posts
    15
    YOU ARE AWESOME ADAM!!!!! Thank you SOOOO much! Worked like a charm! :-) Now I can relax and watch a bit of tv before I go to bed!

  5. #5
    jayO'Brien is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    2
    Boy, I can't make it work. The issue is the same. Name of "O'Brien" and desire to query using it. I started with

    Dim SQL AsString = "SELECT Sex FROM Lots WHERE LotNo = '" & LotNo & "' And Location = & Location & "'"


    tried
    Dim
    SQL AsString = "SELECT Sex FROM Lots WHERE LotNo = '" & LotNo & "' And Location = '" & "'" & Location & "'" & "'"

    and

    Dim SQL AsString = "SELECT Sex FROM Lots WHERE LotNo = '" & LotNo & "' And Location = '" & """" & Location & """" & "'"

    and some other variations. I get a Syntax error (missing operator)

  6. #6
    jayO'Brien is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    2
    I came up with a new way to do it. My SQL statement is now

    Dim SQL AsString = "SELECT Sex FROM Lots WHERE LotNo = '" & LotNo & "' And Location = '" & fParseString(Location) & "'"

    and fParseString is

    Function fParseString(ByVal AString AsString) AsString


    Dim i AsInteger
    Dim l AsInteger
    Dim temp AsString
    i = InStr(1, AString, "'")
    If i > 0 Then
    l = Len(AString) - i
    temp = Mid$(AString, 1, (i))
    temp = temp &
    "'"
    temp = temp & Mid$(AString, (i + 1), l)
    AString = temp
    EndIf
    fParseString = AString
    EndFunction


  7. #7
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could also try:

    Code:
    Function ConvertQuotesSingle(InputVal)
       ConvertQuotesSingle = Replace(InputVal, "'", "''")
    End Function
    Dim SQL AsString = "SELECT Sex FROM Lots WHERE LotNo = '" & LotNo & "' And Location = '" & ConvertQuotesSingle(Location) & "'"




Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  2. Employee Training Records
    By Penn State Girl in forum Database Design
    Replies: 8
    Last Post: 06-06-2011, 08:09 AM
  3. Replies: 5
    Last Post: 04-24-2011, 03:14 AM
  4. Append employee name with '
    By jgelpi16 in forum Programming
    Replies: 8
    Last Post: 02-11-2011, 09:30 AM
  5. Employee Tracking
    By nycon in forum Access
    Replies: 6
    Last Post: 10-27-2010, 05:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums