Solved

ACCESS VBA to handle double quotes in string field

Posted on 2013-10-25
12
899 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
  • 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
 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now