?
Solved

Cleaning out apostrophe before using in where clause in VBA code

Posted on 2011-09-13
7
Medium Priority
?
401 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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