Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Short Name (2003)

    I got the following code for a query defining a short name:
    ShortName: Left(Trim(Replace(Replace([tblCBA.Group Name],"the",""),",","")),24)
    I would like to exclude dashes also. Could someone explain how to add another exclusion so in the future if I want to exclude something else, I can edit the code myself. Right now the code excludes commas and "the" from a company name.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Short Name (2003)

    <P ID="edit" class=small>(Edited by mbarron on 27-Feb-07 15:17. fixed a typo)</P>Add another replace function:

    Left(Trim(Replace(Replace(<font color=red>REPLACE(</font color=red>[tblCBA.Group Name],"the",""),",","")<font color=red>, "-","")</font color=red> ),24)


    The structure for the function:
    Replace(Text you are altering , text you are looking to replace , replacement text)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Short Name (2003)

    MBarron's suggestion will work well if you have one or two additions. If you would like to specify a whole set of characters/phrases that you would like to exclude, I'd create custom VBA function, and perhaps store the items to be replaced in a small table. That way, you'd be much more flexible.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •