ACCESS VBA to handle double quotes in string field

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.
LeLeBrownAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony BerenguelConnect With a Mentor Commented:
try this in your expression.

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

Open in new window

0
 
Anthony BerenguelCommented:
Alternatively you can scrub your columns to get rid of the double quotes.
0
 
LeLeBrownAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LeLeBrownAuthor Commented:
Now I am getting a 3075 error - missing operator.
0
 
Anthony BerenguelCommented:
can you post the snippet of code that's giving you an error?
0
 
Dale FyeCommented:
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
 
Anthony BerenguelCommented:
@fyed: Out of curiosity what does the dollar symbol in do in chr$(34)? I've never seen that before. Thanks!
0
 
LeLeBrownAuthor Commented:
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
 
Anthony BerenguelCommented:
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
 
LeLeBrownAuthor Commented:
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
 
Anthony BerenguelCommented:
Awesome. Glad you're squared away.

Have a great weekend!

ab
0
 
LeLeBrownAuthor Commented:
Thanks for your help!!
Have a great weekend yourself!

Lele
0
All Courses

From novice to tech pro — start learning today.