Dlookup run-time error '3075'

[sq75222]

I try to run Dlookup with Cust = 'ABC' is not problem but I change to 'ABC'S' the error show below:
time error '3075'
syntax error (missing operator) in query expression '[Model] = '121-010' and [Cust] = 'ABC'S'.

[ssanfu]

It has to do with apostrophes, single quotes & double quotes. Because they are delimiters, they must be doubled to be included in the search string (criteria).

This works...... Cust = 'ABC'

This doesn't.....Cust = 'ABC'S' (because of the embedded single quote after the "C")

This works.......Cust = 'ABC''S'

This happens a lot with names like O'Brian, O'Malley, etc.



I have a couple of functions to deal with these issues

For single quotes: (simple)

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

For single quotes: (more advanced)

Public Function ConvertSingleQuotes(strText As Variant)
   On Error GoTo Err_ConvertSingleQuotes
   Dim Pos As Long, strNewText As String

   If IsNull(strText) Then
      ConvertSingleQuotes = Null
      Exit Function
   End If

   strNewText = strText

   Do While InStr(strNewText, "'") > 0
      Pos = InStr(strNewText, "'")
      strNewText = Left(strNewText, Pos - 1) & "''" & Mid(strNewText, Pos + 1)
   Loop

   ConvertSingleQuotes = strNewText

Exit_ConvertSingleQuotes:
   Exit Function
Err_ConvertSingleQuotes:
   MsgBox Err & ", " & Error$
   Resume Exit_ConvertSingleQuotes
End Function

For double quotes:

Public Function ConvertDoubleQuotes(strText As Variant)
   On Error GoTo Err_ConvertDoubleQuotes
   Dim Pos As Long, strNewText As String

   If IsNull(strText) Then
      ConvertDoubleQuotes = Null
      Exit Function
   End If

   strNewText = strText

   Do While InStr(strNewText, """") > 0
      Pos = InStr(strNewText, """")
      strNewText = Left(strNewText, Pos - 1) & "'" & Mid(strNewText, Pos + 1)
   Loop

   ConvertDoubleQuotes = strNewText

Exit_ConvertDoubleQuotes:
   Exit Function
Err_ConvertDoubleQuotes:
   MsgBox Err & ", " & Error$
   Resume Exit_ConvertDoubleQuotes
End Function

[Bob Fitz]

I think the following link will be of interest: https://www.accessforums.net/sql-ser...mes-18237.html
Edit:
Sorry Steve, forgot to check for other replies after finding the link.

[ssanfu]

Bob,

No worries. forgot about that thread (it was a long time ago )

Someplace I have a link to an explanation on delimiters, but I can't find it.



Edit: Maybe this link will also help: http://allenbrowne.com/binary/Access...Chapter_07.pdf

[sq75222]

Thanks Steve but for your function there is an error on the Do While ...Loop casue can not exit from it

[ssanfu]

You're right.. Thanks for pointing that out.
Searching my code, it seems I only use the ConvertQuotesSingle() function in my code, so I will be deleting the functions ConvertSingleQuotes() and ConvertDoubleQuotes() from my code. Yea!!..less code.

I think ConvertSingleQuotes() and ConvertDoubleQuotes() were from an old A97 dB I had found; I guess I never used them.
A97 didn't have the Replace() function (IIRC).

I would stick with the one line "ConvertQuotesSingle" function.


However, this should fix the looping problem:
REVISED CODE

Public Function ConvertSingleQuotes(strText As Variant)
'-----------------------------------------------------------
' This function converts a single quote to two single quotes
'
' This is useful if you want to change O'Brian to O''Brian
'-----------------------------------------------------------

    On Error GoTo Err_ConvertSingleQuotes
    Dim Pos As Long, strNewText As String

    If IsNull(strText) Then
        ConvertSingleQuotes = Null
        Exit Function
    End If

    strNewText = strText
    Pos = 1

    Do While InStr(Pos, strNewText, "'") > 0
        Pos = InStr(Pos, strNewText, "'")
        strNewText = Left(strNewText, Pos - 1) & "''" & Mid(strNewText, Pos + 1)
        Pos = Pos + 2
    Loop

    ConvertSingleQuotes = strNewText

Exit_ConvertSingleQuotes:
    Exit Function
Err_ConvertSingleQuotes:
    MsgBox Err & ", " & Error$
    Resume Exit_ConvertSingleQuotes
End Function



Public Function ConvertDoubleQuotes(strText As Variant)
'---------------------------------------
' This function converts double quotes to single quotes

' This is useful if you have text that is delimited with double quotes
' It will change "John Smith" to 'John Smith'
'---------------------------------------

    On Error GoTo Err_ConvertDoubleQuotes
    Dim Pos As Long, strNewText As String

    If IsNull(strText) Then
        ConvertDoubleQuotes = Null
        Exit Function
    End If

    strNewText = strText
    Pos = 1

    Do While InStr(Pos, strNewText, """") > 0
        Pos = InStr(Pos, strNewText, """")
        strNewText = Left(strNewText, Pos - 1) & "'" & Mid(strNewText, Pos + 1)
        Pos = Pos + 2
    Loop

    ConvertDoubleQuotes = strNewText

Exit_ConvertDoubleQuotes:
    Exit Function
Err_ConvertDoubleQuotes:
    MsgBox Err & ", " & Error$
    Resume Exit_ConvertDoubleQuotes
End Function