Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.