?
Solved

ACCESS VBA to handle double quotes in string field

Posted on 2013-10-25
12
Medium Priority
?
1,178 Views
Last Modified: 2013-10-25
I have a Access table that I am reading in with First Name, Last Name and DOB. A few of the first and last names are stored like this:  "John"   "Doe"   with the double quotes in the field in the table. How can I query to find this row? I have tried """ & FirstName & """. It doesn't error but doesn't find the row either.
0
Comment
Question by:LeLeBrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 2000 total points
ID: 39601203
try this in your expression.

" & chr(34) &  FirstName & chr(34) & "

Open in new window

0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39601212
Alternatively you can scrub your columns to get rid of the double quotes.
0
 

Author Comment

by:LeLeBrown
ID: 39601226
Thanks. I will try this. I did do a replace to get rid of them but I think when  read the table and the table fields still have them, it does not return anything. I tried putting the replace on the table side as well (in the VBA sql) but it didn't work either.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:LeLeBrown
ID: 39601255
Now I am getting a 3075 error - missing operator.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39601261
can you post the snippet of code that's giving you an error?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39601268
I agree with aebea.

Update yourTable
SET [First Name] = Replace([First Name] & "", chr$(34), ""),
       [Last Name] = Replace([Last Name] & "", chr$(34), "")

The replace function doesn't handle NULLs, so by adding an empty string to the end of the [First Name] and [Last Name] fields inside the Replace function you will avoid error messages.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39601279
@fyed: Out of curiosity what does the dollar symbol in do in chr$(34)? I've never seen that before. Thanks!
0
 

Author Comment

by:LeLeBrown
ID: 39601311
In my VBA I am actually reading a query. This query has a few tables joined. Before the sql I set the where fields (LastName and FirstName).

FirstName = me.txtFirstName.value
FirstName = Replace(FirstName,"""","")
LastName = me.txtLastName.value
LastName = Replace(LastName,"""","")

Select * from QryForName where LName = " & Chr(34) & LastName & chr(34) & " and FName = " & chr(34) & FirstName & chr(34) & " "

I have also stripped the double quotes from the names in the query using the replace just like above.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39601332
Can you post the entire module that this snippet comes from? Or answer the following for me:

Let's call this step01:
FirstName = me.txtFirstName.value
FirstName = Replace(FirstName,"""","")
LastName = me.txtLastName.value
LastName = Replace(LastName,"""","")

Open in new window

And let's call this step02:
Select * from QryForName where LName = " & Chr(34) & LastName & chr(34) & " and FName = " & chr(34) & FirstName & chr(34) & " "

Open in new window

In your code do these two steps happen one after another? If so that is you're problem.


Also, step01 should look like this
That and there's a little syntax problem with step01 as it is. Really it should look like:
FirstName = Replace(me.txtFirstName.value,chr(34),"")'replace double quote with empty string
LastName = Replace(me.txtLastName.value,chr(34),"")'replace double quote with empty string

Open in new window

0
 

Author Closing Comment

by:LeLeBrown
ID: 39601354
It is working. I made a mistake at first. I changed the wrong query with the replace. Once I found the right query, put the replace in,  it worked coupled with the replace in the VBA code.

Thanks!
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39601367
Awesome. Glad you're squared away.

Have a great weekend!

ab
0
 

Author Comment

by:LeLeBrown
ID: 39601377
Thanks for your help!!
Have a great weekend yourself!

Lele
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

719 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