Solved

Filtering Access Recordset when Data Contains both Apostrophe and Quotes?

Posted on 2011-09-28
4
500 Views
Last Modified: 2012-05-12
Hello Experts

I have a table with a text field that has data imported into from an outside source (XLS spreadsheet). I then have a function that loops through this table and searches for occurences of certain text strings. The problem I am having is that sometimes the text strings being searched have quotes in them:
i.e. - 17" rope cord

and sometimes the text strings have single quotes in them
i.e. I's rope cord's

I previously had this code:
 
'Me.strVDestColumn is the column name being searched
'rsProducts(strVSourceColumn) is the text string the recordset is looking for
strCriteria = "[" & Me.strVDestColumn & "] = '" & rsProducts(strVSourceColumn) & "'"
                        
                        'Filtering main product recordset for potential matching data
                        prsMainProducts.FindFirst strCriteria

Open in new window


This code works fine with the double quotes, but fails on single quotes.

How do I universally filter/search for text strings that may have single quotes, may have double quotes, or may have neither?

Thank you!
0
Comment
Question by:w00tw00t111
[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
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36719818

try this


strCriteria = "[" & Me.strVDestColumn & "] =" & chr(34) & rsProducts(strVSourceColumn) & chr(34)
0
 

Author Comment

by:w00tw00t111
ID: 36719834
Capirocorn,

Thank you for your reply. That was my original criteria string and it works fine when there is a single quote in the string, but fails when there is a double quote in the string, such as 17" Rope Cord because the resulting criteria is:

[ItemName]= "17"RopeCord"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36719858
try this

strCriteria = "[" & Me.strVDestColumn & "] =" & chr(34) & Replace(rsProducts(strVSourceColumn),chr(34),chr(34) & chr(34)) & chr(34)
0
 

Author Comment

by:w00tw00t111
ID: 36719921
Phew! That did it Capricorn! Thank you so much! (Spent several hours on that today with no luck -_-) Thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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