Equivalent of Excel TRIM function in Access?

[SLARRA]

I have an Access database that includes a table of remarks entered by users via a form that did not have the feature enabled that allowed a user to hard return (Enter Key Behavior was NOT set to New Line in Field). As a result, some users entered a lot of spaces when they had multiple themes in a single comment to force a (displayed) wrap to a new line to separate those themes in the form field. Of course, all those spaces were stored in the table's field and now, when I call that table's contents into a new report, those comments display horribly. In Excel, I could drop out all those extra spaces using the TRIM function. In Access (assuming I understand this correctly, which may not be true), the TRIM function only removes extra spaces at the beginning and ending of the memo field records - that is, it will not remove those long strings of spaces that are in the middle of the comments. I can't use ACCESS's REPLACE function for a one-time cleanup because each set of spaces is a variable width (i.e. if I search for instances of 2 consecutive spaces and replace them with a single space, I'd have to run that function three times to get all the 8 space strings reduced to 4, then to 2, then to just 1).

While the immediate problem is resolving how these fields display on my new report, I should ultimately update the table to replace these space-y historic records with a cleaned-up (single spaces) version. I have changed the properties on the data collection form to allow hard returns, so I think future records will be collected cleanly.

Curiously, if I copy the table's comments field (whole column) from Access to Excel and paste matching the (default) destination formatting, Excel does automatically cleans up those records. If Excel can figure that out on the fly, I'd like Access to have a text/memo box property for reports that could do the same, but I don't think any such functionality exists in Access.

So... ideally without resorting to VBA, any ideas on how to get those space-y comments to render with just single spaces on my Access report?

Thanks!

SDL

 

[Joe4]

I can't use ACCESS's REPLACE function for a one-time cleanup because each set of spaces is a variable width (i.e. if I search for instances of 2 consecutive spaces and replace them with a single space, I'd have to run that function three times to get all the 8 space strings reduced to 4, then to 2, then to just 1).

That is your non-VBA solution right there, running that function multiple times.

Otherwise, I think you are stuck using VBA. Note that does not always mean running some sort of VBA code or macro. That could mean creating a custom User Defined Function to do it, and then just calling that Function from a normal query.

Here are a few links you may want to look at:
MS Access SQL: use update and replace to remove multiple spaces with a single one - Stack Overflow
UtterAccess Forums > VBA To Remove Replace Multiple Inner Spaces With One Space ...

 

[SLARRA]

Thanks for the reply and the suggestion to code to a User Defined function. I get a little intimidated by VBA, being a minimally-competent Access user and utter novice with VBA. Frequently, my fixes involve importing the offending records to Excel (where my skills are much stronger), cleaning them up, and then pushing them back into Access. It makes me cringe to do things that way, but it also keeps me from wasting hours trying to figure out how to do what should be simple tasks in the frustrating world of Access.

I should mention that the links are very helpful!

Thanks!

SDL

 

[Joe4]

I should mention that the links are very helpful!

Thanks!

You are welcome.

Hopefully, you can find a User Defined Function (UDF) in those links that looks like it does what you want. Then it is just a matter of copying and pasting it into a VBA module, and then you can use it like any other Access function (without having to really understand any VBA code at all).

If you run into any issues getting it to work, let us know which UDF you are using, and post your Query Formula, and we'll see if we can help you fix it up.

but it also keeps me from wasting hours trying to figure out how to do what should be simple tasks in the frustrating world of Access.

Access is quite powerful, but it is important to understand that it is very different animal than Excel (it is not a "substitute" for Excel, but rather a relational database program). Like anything else, if you are unfamiliar with it, there will be moments of frustration!