Trimming Extra Spaces within a Text String

[jobb]

The Trim function in Access only removes extra spaces at the beginning and ending of a text string.

If I have a text string like this,

"JOHN Q PUBLIC"

Is there any way I can remove the extra spaces inside the string, so it reads like this:

"JOHN Q PUBLIC"

 

[Dugantrain]

You're looking to trim "JOHN Q PUBLIC" down to "JOHN Q PUBLIC"? Am I missing something here?

 

[jobb]

that's odd, this website seems to do exactly what I'm trying to do

Okay, I will substitute dashes for spaces..

I want to trim

"JOHN-------------Q----------------PUBLIC"

to

"JOHN Q PUBLIC"

 

[Joe4]

Check out this solution I found on the internet:

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=149

 

[jobb]

thanks for the solution..

I have little experience with modules. Can you show me how this would work in a module?

thanks again

 

[Joe4]

Go into the Modules object, click on New, and paste teh following code in the resulting window (probably called Module1):

Function MyTrim(str As String) As String

    'Start by trimming leading/trailing spaces
    str = Trim(str)
    
    'Now, while we have 2 consecutive spaces, replace them
    'with a single space...
    Do While InStr(1, str, "  ")
      str = Replace(str, "  ", " ")
    Loop

    MyTrim = str

End Function

Save and close the module.

Now you can use the "MyTrim" function exactly as you would the "Trim" function in queries or VBA, i.e.
MyTrim(value)

Note: value will probably be a field name

 

[jobb]

Thanks,

I tried this, but it doesn't recognize the "REPLACE" function.

I'm using Access 97

 

[Joe4]

Hmmm... I am using Access 2002, so I can't recreate the problem. I did find some links which look like they have more code to create the Replace function. Here they are:

http://support.microsoft.com/default.aspx?scid=kb;en-us;109330&Product=acc97

http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/705/fid/1824

http://forums.devshed.com/t98830/s6c092598a64781a322a1bf69172d6405.html

You would need to place one of these in the module as well. Then, wherever the MyTrim function calls "Replace", replace it with the name of this new module (i.e. sReplace, fReplace, or whatever it is called).

 

[jobb]

cool... it works!

thanks for your help