Link to home
Start Free TrialLog in
Avatar of LeLeBrown
LeLeBrown

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alternatively you can scrub your columns to get rid of the double quotes.
Avatar of LeLeBrown
LeLeBrown

ASKER

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

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!
Awesome. Glad you're squared away.

Have a great weekend!

ab
Thanks for your help!!
Have a great weekend yourself!

Lele