Access 2003 dlookup does not like names with an apostrophe

I have an Access 2003 DB, and in the main form I have several drop down boxes that do a DLOOKUP to another table and return data, populating several fields automatically. This has been working great until today. I tried to put a name in that contained an apostrophe, IE: St. George's School, and when I try to select that on the drop down, Access errors and says "missing operator". Any idea how I can get around this, many of the names I will enter contain an apostrophe. DLOOKUP code is shown below.
thx experts...
-Bob
LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = '" & [LocName2] & "'")
City2 = DLookup("LocCity", "tblLocation", "LocName = '" & [LocName2] & "'")
LocState2 = DLookup("LocState", "tblLocation", "LocName = '" & [LocName2] & "'")
LocZip2 = DLookup("LocZip", "tblLocation", "LocName = '" & [LocName2] & "'")

Open in new window

bobrossi56Asked:
Who is Participating?
 
RDWaibelCommented:
Very True.
I have a function that will fix that for you.

This code should be added to a new module.


LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " & acsdata([LocName2]))
City2 = DLookup("LocCity", "tblLocation", "LocName = " & acsdata([LocName2]))
LocState2 = DLookup("LocState", "tblLocation", "LocName = " & acsdata( [LocName2]))
LocZip2 = DLookup("LocZip", "tblLocation", "LocName = " & acsdata([LocName2]))
Global Const ACS_DATE_FORMAT As String = "\#mm/dd/yyyy\#"
Global Const ACS_DATETIME_FORMAT As String = "\#mm/dd/yyyy Hh:Nn:Ss\#"
 
Public Function ACSData(ByVal DataVal As Variant, Optional ByVal DataType As Integer) As String
 
    Dim result As String
    Dim QuotePos As Integer
    
    If DataType = 0 Then
        DataType = vbString
    End If
    
    If IsNull(DataVal) Then
        result = "NULL"
    Else
        Select Case DataType
            Case vbNull
                result = "NULL"
            Case vbString
                result = DataVal & ""
                QuotePos = InStr(1, result, "'")
                Do While QuotePos > 0
                    result = Left(result, QuotePos) & "'" & Mid(result, QuotePos + 1)
                    QuotePos = InStr(QuotePos + 2, result, "'")
                Loop
                If result = "" Then
                    result = "NULL"
                Else
                    result = "'" & Trim(result) & "'"
                End If
            Case vbDate
                result = Format(DataVal, ACS_DATE_FORMAT)
            Case vbBoolean
                result = Abs(CInt(DataVal)) & ""
            Case vbVariant
                result = Format(DataVal, ACS_DATETIME_FORMAT)
            Case vbLongDate
                result = Format(DataVal, ACS_DATETIME_FORMAT)
 
            Case Else
                result = Trim(DataVal & "")
        End Select
    End If
    
    ACSData = result
    
End Function

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " & Char(34) & [LocName2] & Char(34))

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Also try:

LocAdd2 = DLookup("LocStreet", "tblLocation", "Replace(LocName,"'","") = '" & Replace([LocName2],"'","") & "'")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rockiroadsCommented:
I think Mikes first post attempted to solve this for you. Wrap your string that contains quotes using double quotes

Use chr$(34) instead of char(34)
0
 
rockiroadsCommented:
also noticed missing double quote

something like this

LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " chr$(34) & [LocName2] & chr$(34))
0
 
bobrossi56Author Commented:
Tried this, got a compile error, sub or function not defined, and Char(34) is highlighted.
0
 
rockiroadsCommented:
My recommendation however is to use recordsets instead of multiple calls to one table as that is slower
eg



dim rs as dao.recordset
set rs=currentdb.openrecordset("select * from tblLocation where LocName = " & chr$(34) & Me.LocName2 & chr$(34))
if rs.eof = true then
    msgbox "Location not found"
else
    LocAdd2 = rs!LocStreet
    City2 = rs!LocCity
    LocState2 = rs!LocState
    LocZip2 = rs!LocZip
endif

rs.close
set rs=nothing

0
 
bobrossi56Author Commented:
RD.. I created a new module, named it module2 and pasted your code into it, but I get the same error. I wanted to use this solution as it was a global solution and I would not have to edit all the DLOOKUP code.
0
 
RDWaibelCommented:
But you will have to edit the dlookup code.

You will need to replace the "LocName = '"... with "LocName = " & ACSData([LocName2])


' Your Code
LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = '" & [LocName2] & "'")
City2 = DLookup("LocCity", "tblLocation", "LocName = '" & [LocName2] & "'")
LocState2 = DLookup("LocState", "tblLocation", "LocName = '" & [LocName2] & "'")
LocZip2 = DLookup("LocZip", "tblLocation", "LocName = '" & [LocName2] & "'")
 
My Code (Solution)
LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " & ACSData([LocName2]))
City2 = DLookup("LocCity", "tblLocation", "LocName = " & ACSData([LocName2]))
LocState2 = DLookup("LocState", "tblLocation", "LocName = " & ACSData( [LocName2]))
LocZip2 = DLookup("LocZip", "tblLocation", "LocName = " & ACSData([LocName2]))

Open in new window

0
 
rockiroadsCommented:
Bob, I recommend using a recordset, on read of the table instead of 4 expensive reads
0
 
RDWaibelCommented:
Rockiroads does have a point with the record set idea, IF you understand how to do that.
0
 
bobrossi56Author Commented:
I understand what you are saying rock, but this is a real small, single user DB and speed is not an issue. Plus, I don't want to have to rewrite all the code. And lastly, I am a novice and I don't really understand how to incorporate your suggesiton.
0
 
RDWaibelCommented:
Bob,
were you able to get my solution working??
0
 
bobrossi56Author Commented:
RD, I tested it in my test DB and it worked. Tonight I will edit all the code in the live DB and if all goes well I will close the question tomorrow and award the points.
-Bob
0
 
rockiroadsCommented:
In response to your last comment then, I can explain to you the use of recordsets if you wish. This is the better way to develop even though you think multiple dlookups is easier. But only if you want me to.

Regardless of above, did you try just using chr$(34) ? I really dont see why a function is required here.  If you want to use multiple dlookups

LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " & Chr$(34) & [LocName2] & Chr$(34))
City2 = DLookup("LocCity", "tblLocation", "LocName = " & Chr$(34) & [LocName2] & Chr$(34))
LocState2 = DLookup("LocState", "tblLocation", "LocName = " & Chr$(34) & [LocName2] & Chr$(34))
LocZip2 = DLookup("LocZip", "tblLocation", "LocName = " & Chr$(34) & [LocName2] & Chr$(34))


But better still, why keep generating the criteria

Dim sWhere As String


sWhere = "LocName = " & Chr$(34) & LocName2 & Chr$(34)

LocAdd2 = DLookup("LocStreet", "tblLocation", sWhere)
City2 = DLookup("LocCity", "tblLocation", sWhere)
LocState2 = DLookup("LocState", "tblLocation", sWhere)
LocZip2 = DLookup("LocZip", "tblLocation", sWhere)


0
 
bobrossi56Author Commented:
Sure, always yearning to learn anything I can rockiroads. Are you saying that in the VB code I can just drop in your code example, and eliminate the multiple lines of DLOOKUP? Of course I would have to modify the field and table names in our example as applicable.
thx...Bob
0
 
rockiroadsCommented:
Yes, you can just drop it in

Let me explain the dlookup

LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = '" & [LocName2] & "'")

This is saying get me LocStreet from tblLocation where LocName is somevalue
this is the same as this query

select LocStreet from tblLocation where locName = somevalue

you can use this in a recordset and get the value

This is just one read from the table

Now if you want to get a value from a differnt column in the table, you just extend the recordset. It might be easy to code from a novice point of view just to throw another dlookup and of course it does work, but its considered bad practice. Why make two reads of a table when it can be done in one?

To extend the query, if you did   >> select * <<  this returns all columns. You can of course just specify the columns you want.

Ok, her is that code with more explanation, hopefully it makes sense.
Yes it looks like more code but any experienced programmer will tell you this is more efficient than doing multiple reads



'Need to define the recordset variable
Dim rs As dao.Recordset

'Create avariable to hold our generated sql
Dim sSql As String


'Now we create our sql i.e. as described in the dlookup comparison above
sSql = "select * from tblLocation where LocName = " & Chr$(34) & LocName2 & Chr$(34)

'Now we initialise our recordset, run the sql and assign results back to rs
Set rs = CurrentDb.OpenRecordset(sSql)

'Check the End of File indicator. If set this means no rows found
If rs.EOF = True Then
    MsgBox "Location not found"
Else

    'To reference a column from your query, you access it via the recordst
    'Syntax is recordset variable ! columnname
    LocAdd2 = rs!LocStreet
    City2 = rs!LocCity
    LocState2 = rs!LocState
    LocZip2 = rs!LocZip
End If

'Now that we done our work, we must close down gracefully

'Close our recordset
rs.Close

'Clear it down, free up some me
Set rs = Nothing




0
 
rockiroadsCommented:
Sorry, a lot to take in, a big post. If I didnt think it was worthwhile I wouldnt of bothered posting. Please take your time in trying to understand.
0
 
bobrossi56Author Commented:
OK, I think I have it, basically you are opening up a read to a table, and reading the entire table into a variable "recordset", then you can grab from the recordset what you need, not having to do multiple reads to the table, and when you are done you close down the recordset and set it to equal nothing.

Am I close???
0
 
rockiroadsCommented:
Getting there.

We are not reading the whole table, if you wantd to do that we would just do

select * from tblLocation

but since we added filtering (WHERE ...), it returns all rows that match that criteria. In this case it is one. Same as your dlookup.

The recordst holds the values of all the rows returned. If there was multiple rows, you would move through using simple commands like rs.MoveNext.
In this case though you wont need to do this

Then as you said, grab what you want from the recordset, and yes this is not doing reads to the table

and once done, we close down, yep!
0
 
bobrossi56Author Commented:
OK, I have it now. I will give it a try tonight.
thx
0
 
bobrossi56Author Commented:
Both of these solutions worked, so I split the points. Thx folks....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.