Solved

ACCESS VBA to handle double quotes in string field

Posted on 2013-10-25
12
1,091 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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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 …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

623 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