Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I have a need to create a simple find and replace word table, tblFindReplace . The table will have a FindText field and a ReplaceWith Field. This table will be used against a CompanyName text field to create a standardized Short Name value in a Client table. For example replace "First" with "1st", "Company" with "Co" etc. There are some cases inwhich the user would like to replace a value with a new value ending in one or more blanks. I have the ReplaceWith field defined as accepting a Zero length string and Required set to No. However, when one types a ReplaceWith value with one or more ending blanks, they are being retained. Is there some setting I have failed to set properly to support trailing blanks? If trailing blanks are not supported, is there a better approach than using some character to signify a blank and replacing it with a blank using code in a two pass process?

    Marty

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    I would consider adding a check box for adding trailing blanks in your replace with value. The user would type only the characters for the ReplaceWith value and then check the box if he wanted trailing spaces. You could then add the trailing blanks using a concatenation formula. This would normalize the imput and give you control over the trailing blanks.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    How about an underscore?

    So something like this

    Code:
    strInput = Replace(strInput, strReplaceWhat, strReplacementWith)
    strInput = Replace(strInput, "_", " ")

  4. #4
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Access really doesn't support storing trailing spaces in a text field. It uses a variable lenth text string and trims all trailing spaces. You can force them in by using an "INSERT INTO" SQL statement but the minute someone manually edits the data in the field, it will trim off the trailing spaces.

    As for a solution, you could add a field to your table to store the number of spaces that the user typed into the field and then use that number to pad the field later by concatenating the field value with that number of spaces using the "Space" function. That seems pretty cumbersome however and would need to be maintained anywhere you allowed the user to touch/edit the field.

    There are probably better solutions as this is one thing I have never wanted, except when exporting to a fixed width text file.

    Hope that helps.

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

Posting Permissions

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