Retaining Trailing Blanks

[mcowen]

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

[caveman144]

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.

[kentg]

How about an underscore?

So something like this

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

[BobOxford]

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