substring function in VBA? - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

substring function in VBA?

blueknightblueknight Posts: 1Member
I'm new to vba - using it with access. I'm building sql statements on the fly. Sometimes the dynamic text has a ' (quote) in it (O'Toole). How do I escape that out in vba. I looked for a substring command, didn't find it. I'm using access 2000 and its vb.

Comments

  • MozMoz Posts: 32Member
    : I'm new to vba - using it with access. I'm building sql statements on the fly. Sometimes the dynamic text has a ' (quote) in it (O'Toole). How do I escape that out in vba. I looked for a substring command, didn't find it. I'm using access 2000 and its vb.
    :
    :
    Try looking up the help on the Instr, len, right and/or left commands in VBA. Not sure exactly what you're trying to do, but if you're looking for a substring function, you will find the answer in the these. InStr looks for a matching string or character; Len defines the length of a string; and left and right get a substring based on a start position (which can be found from instr).

    Hope this helps,
    Moz.
  • Ara_camAra_cam Posts: 37Member
    : : I'm new to vba - using it with access. I'm building sql statements on the fly. Sometimes the dynamic text has a ' (quote) in it (O'Toole). How do I escape that out in vba. I looked for a substring command, didn't find it. I'm using access 2000 and its vb.
    : :
    : :
    : Try looking up the help on the Instr, len, right and/or left commands in VBA. Not sure exactly what you're trying to do, but if you're looking for a substring function, you will find the answer in the these. InStr looks for a matching string or character; Len defines the length of a string; and left and right get a substring based on a start position (which can be found from instr).
    :
    : Hope this helps,
    : Moz.
    dim x as string
    dim y as string
    dim n as integer
    y="O'Toole"
    x="'"
    n = InStr (1, y, x, vbTextCompare

    This will tell you if the the value in X is in the string Y, and what position it is first found at.

    y = Replace(y, x, ,":")
    This will replace all occurances of X within Y with : (or whatever is in the third variable). Other options exist for the Replace command, so check the help file.

    Hope this helps!




  • SilentRageSilentRage Posts: 13Member
    yeah, rather than replacing the ' with ":" you may replace it with nothing. I use replace often to strip characters.

    Variable = Replace("O'Tool", "'", "")
Sign In or Register to comment.