?
Solved

Cleaning out apostrophe before using in where clause in VBA code

Posted on 2011-09-13
7
Medium Priority
?
393 Views
Last Modified: 2012-08-14
The below code works as I want.  However, sometimes the users input leading apostrophes in the merchante names and I need to clean these out of each name as the code loops through the listbox and addng to the where clause.  That is, if the strArray(i) is the name *'Merhcanet'*, I need to get the of the two ' before using it in the where clause.
Private Function WhereLikeString() As String
'If there is more than one item in the listbox, then need to create like strings for each item
'in the list box as a parameter to the sql statement
Dim i           As Integer
Dim strWhere    As String
Dim strArray() As String
    
    For i = 0 To lstMerchants.ListCount - 1
        ReDim Preserve strArray(i)
        strArray(i) = lstMerchants.ItemData(i)
        strWhere = strWhere & "GLMerchant Like '" & strArray(i) & "' OR "
        Debug.Print strWhere
    Next i

WhereLikeString = " AND " & Left(strWhere, Len(strWhere) - 4)
Debug.Print WhereLikeString

End Function

Open in new window

0
Comment
Question by:ssmith94015
[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
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36532947
strArray(i) = Replace(lstMerchants.ItemData(i) , Chr(39), "")

mx
0
 
LVL 28

Expert Comment

by:omgang
ID: 36532958
The VBA Replace function should do what you want.  Search VBA Help for more info.

strArray(i) = Replace(lstMerchants.ItemData(i), "'", "")

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 36532964
Of course, 'MX not only beats me to it but he also remembers the ASCII code for apostrophe......
OM Gang
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 36532977
And if the merchant's name is
Smith's Merchants
what would be appropriate?  Removing the quote mark leaves you with
Smiths Merchants
Which will presumable not match the stored value?

Are you saying that there are stored values in the table (Merhcanet) which don't correspond to the inaccurately typed text ('Merhcanet') of a user searching for those?
Would you not want to just check that the entered text isn't delimited with quotes?

Just wondering...
0
 

Author Closing Comment

by:ssmith94015
ID: 36533034
Yep, DatabaseMX had. Also, if I clear out the ' first from the names as well as when running the loop, both match despite what the user has inputted.  Fortunately, the keys between tables are on unqiue identifiers and any addresses for communication come from another table, so I can kill the apostrohies as I like.

Sandra
0
 
LVL 21
ID: 36533073
Two things:

1) I use  IN() instead of Like

2) When building an SQL statement I always use Quotes  ( " ) as the text delimiter not an apostrophe ( ' ).   When building the SQL sting in Code you would double the Quotes to get it into the string.  

In your can I would use something like:

strArray(i) = Replace( lstMerchants.ItemData(i), Chr(34), Chr(34) & Chr(34))

Open in new window


or is you do want to use an apostrophe as the text delimiter:

strArray(i) = Replace( lstMerchants.ItemData(i), Chr(39), Chr(39) & Chr(39))

Open in new window




0
 

Author Comment

by:ssmith94015
ID: 36533107
Thank TheHiTechCoarch, but merchants can be called  Merchant Today, Merchant Tomorrow, Merchange Yesterday but I want all Merchants for this group, but I don't know specifics so I do know that like *Merchant* will capture what I am looking for.  Bascially, the end-users have groups of merchants under names, but all would have one phrase the same, like Raley would be Raley South, Raley North, Raley East, but I want everything like Raley, so the IN won't work in this scenario.  Also, I inherited this code and they started with an apostrophe all over the place so I am going with that for now.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

770 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