?
Solved

Access 2003 dlookup does not like names with an apostrophe

Posted on 2009-04-30
22
Medium Priority
?
578 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:bobrossi56
  • 8
  • 8
  • 4
  • +1
22 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 24270921
try:

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

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 24270941
Also try:

LocAdd2 = DLookup("LocStreet", "tblLocation", "Replace(LocName,"'","") = '" & Replace([LocName2],"'","") & "'")
0
 
LVL 14

Accepted Solution

by:
RDWaibel earned 1000 total points
ID: 24270954
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24270960
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24270974
also noticed missing double quote

something like this

LocAdd2 = DLookup("LocStreet", "tblLocation", "LocName = " chr$(34) & [LocName2] & chr$(34))
0
 

Author Comment

by:bobrossi56
ID: 24270980
Tried this, got a compile error, sub or function not defined, and Char(34) is highlighted.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24270999
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
 

Author Comment

by:bobrossi56
ID: 24271074
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
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24271345
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24271422
Bob, I recommend using a recordset, on read of the table instead of 4 expensive reads
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24272113
Rockiroads does have a point with the record set idea, IF you understand how to do that.
0
 

Author Comment

by:bobrossi56
ID: 24272121
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
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24272325
Bob,
were you able to get my solution working??
0
 

Author Comment

by:bobrossi56
ID: 24273153
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24273425
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
 

Author Comment

by:bobrossi56
ID: 24273459
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1000 total points
ID: 24273649
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24273764
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
 

Author Comment

by:bobrossi56
ID: 24273820
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24273875
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
 

Author Comment

by:bobrossi56
ID: 24273902
OK, I have it now. I will give it a try tonight.
thx
0
 

Author Closing Comment

by:bobrossi56
ID: 31576474
Both of these solutions worked, so I split the points. Thx folks....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

809 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