Solved

Cleaning out apostrophe before using in where clause in VBA code

Posted on 2011-09-13
7
383 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 - Access MVP) 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

810 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