Solved

Cleaning out apostrophe before using in where clause in VBA code

Posted on 2011-09-13
7
390 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 500 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

729 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