need help with MID function not working as expected 1

[DougP]

I want to replace certain characters with a space or nothing. But when the code gets to Case "'", "(", ")"
it does nothing it does not replace a single quote with nothing it leaves it alone.
so the string still has a single quote in it.
My Dog's house
becomes
My_Dog's_house
I need it to be
My_Dogs_house (no single quote)

here is my code

    For a = 1 To Len(data1)
        Select Case Mid(data1, a, 1)
            Case " ", "#", "&"
                Mid(data1, a, 1) = "_"
            Case "'", "(", ")"
                Mid(data1, a, 1) = ""    'single quote: remove and put nothing
        Case Else
            'do nothing, characters are normal
        End Select
    Next

TIA

DougP, MCP, A+

I love this site and all you folks that helped me over the years!

 

[lespaul]

why not use the REPLACE function instead?

I would look up the exact syntax, except due to licensure reconciliations I don't have Access on my PC anymore!

I think it would be:

Replace(data1, "'", "")

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[JoeAtWork]

The Mid function retrieves values, it doesn't insert them.

The following

Mid(data1, a, 1) = "_"

merely equates to True or False, it does nothing to data1.

 

[PHV]

Joe, you confuse the Mid instruction with the Mid function.

 

[JoeAtWork]

Huh, never knew there was a Mid statement as well as a Mid function. Somehow it just doesn't seem like a good idea to have the same names for two different things in a programming language.

 

[PHV]

Anyway, to remove a character, replace this:
Mid(data1, a, 1) = ""
with this:
data1 = Left(data1, a - 1) & Mid(data1, a + 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886