Text Functions

M

MGFoster

Kirk said:
Is there a function to remove spaces within a text field?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There isn't a built-in one. Try this:

Function NoSpaces(ByVal s As String) As String

Const WHITESPACE = " "

s = Trim$(s)

Dim p As Long
p = InStr(s, WHITESPACE)
Do While p > 0
s = Left$(s, p - 1) & Mid$(s, p + 1)
p = InStr(p, s, WHITESPACE)
Loop

NoSpaces = s

End Function



--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiYmQYechKqOuFEgEQK2fwCgom0kccXof542/iDl4WUxG1inl/gAoNFz
zok80z7tPp2+Ero3V5l3Dr4r
=IxoP
-----END PGP SIGNATURE-----
 
K

Kirk P.

The function runs, but returns the same text string that I started with. I
have determined that the space is ALWAYS the first character. I would think
the Trim or LTrim function would remove it, but it does not.

Any other ideas? There is definately a space before the first character.
 
F

fredg

Is there a function to remove spaces within a text field?
Click to expand...

If you have Access 2000 or newer you can use the Replace Function.

[FieldName] = Replace([FieldName]," ","")

This will remove ALL spaces within the string.
See VBA help for the additional arguments needed to start at a
specific point or to limit the number of times this will run in a
field.
 
J

John Spencer (MVP)

Then the first character is NOT a standard space character. You might try
modifying the function you were given. Open your table, select the character
that you see as a space, open the function and paste it into the Const
WHITESPACE = " " after you remove the space that is there. Now try using the function.

IF EVERY record has the extra character at the beginning, you could try using
the Mid function.

UPDATE SomeTable
SET YourField = Mid(YourField,2)
 
Top