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
Solved

Cleaning out apostrophe before using in where clause in VBA code

Posted on 2011-09-13
7
384 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 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