Replace Spaces.... 1

[dodgyone]

Does anybody have any ideas about replacing all spaces in fields for all records?

I have a field which could have spaces any where in it (there are characters inbetween the spaces etc). Does anyone have some simple code to remove any spaces? I don't want to put anything in their place... just delete them all, that's it.

Many thanks,
Marcus

 

[tlbroadbent]

OPen a module and insert the following code.

Public Function ReplaceSpaces (sInput as string) As string
ReplaceSpaces=Replace(sInput," ","&quot
End Function

Then create a query like the following.

Update table1
Set col1=ReplaceSpaces(col1), col4=ReplaceSpaces(col4),
Col5=ReplaceSpaces(col5), ... etc.
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dodgyone]

When I try this it says tha the sub or function is not defined... i followed it as you have typed it though..

Thanks for your help...

 

[tlbroadbent]

You must be running an Access version prior to Access 2000. Try function this instead.

Public Function ReplaceSpaces(sInput As String) As String
Dim strRet As String, strChar As String
Dim intI As Integer, intC As Integer

intI = Len(sInput)
For intC = 1 To intI
strChar = Mid(sInput, intC, 1)
If strChar <> &quot; &quot; Then
strRet = strRet & strChar
End If
Next

ReplaceSpaces = strRet
End Function
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[dodgyone]

Sorryy, but I'm not too sure what you mean when you mention the query... how do you set up the query?

Many thanks for helping this old fool = ;o)

 

[dodgyone]

Thanks for your help but I need a slightly different solution to what was mentioned previously...

The records have been transferred into Access from an older database system. The older database seems to of added spaces to the end of every record field in order to cover the whole of the text format length set (so some record fields cover all of the format length and others are shorter and have been given extra spaces to fill the gap at the end). It's crazy and I don't understand why etc but I have no access to the old database anymore to find the answer why!!!!

Is it possible to somehow delete all of the spaces at the end of each record field? I wondered if it could be coded to start from the right and go back, looking to see whether it is a space... if so deleting this and if coming across either a letter, number or misc character stopping the deletion and going onto the next record. There are additional spaces in the record fields which need to be kept... hence not wanting a total deletion of all spaces.

I hope this makes sense...

Thanks

 

[Craig0201]

Dodgy,

Try the following code in a module:

Function TrailingSpaceDelete(Field As String)

Do While Right(Field,1) =&quot; &quot;
Field = Left(Field,Len(Field)-1)
Loop

TrailingSpaceDelete = Field

End Function

Then put the function into your query, just like you'd add any normal function.

HTH

Craig

 

[Craig0201]

Oops!!! Forgot RTrim.....

Use RTrim (YourFieldName) in your query and all will be fine.

Craig

 

[dodgyone]

Thank you... just what I needed. It worked like a treat ;o)

Cheers for the example Craig...