add a space between characters / numbers

mike UH · External Since: Nov 04, 2009 Posts: 1

I have a field called Customer Number with Text / alpha numeric data
type, which varies in length. i need to insert a space between the
characters.

example
1234 becomes 1 2 3 4
asdf123 becomes a s d f 1 2 3

please help me on how to solve this problem.

Thanks
Mike

fredg · External Since: Jan 30, 2004 Posts: 3580

On Wed, 4 Nov 2009 11:48:12 -0800 (PST), mike UH wrote:

> I have a field called Customer Number with Text / alpha numeric data
> type, which varies in length. i need to insert a space between the
> characters.
>
> example
> 1234 becomes 1 2 3 4
> asdf123 becomes a s d f 1 2 3
>
> please help me on how to solve this problem.
>
> Thanks
> Mike

Create a User Defined function in a module:

Public Function AddSpaces(DataIn As String) As String
Dim intX As Integer
For intX = 1 To Len(DataIn)
AddSpaces = AddSpaces & Mid(DataIn, intX, 1) & " "
Next intX
End Function

Add Error Handling as needed.

Call it from the AfterUpdate event of the control on your form:

[ThisControlName] = AddSpaces([ThisControlName])

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Dorian · External Since: Jun 09, 2006 Posts: 16

Sounds weird. Why do you need to do that? Is this for a report?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

"mike UH" wrote:

> I have a field called Customer Number with Text / alpha numeric data
> type, which varies in length. i need to insert a space between the
> characters.
>
> example
> 1234 becomes 1 2 3 4
> asdf123 becomes a s d f 1 2 3
>
> please help me on how to solve this problem.
>
> Thanks
> Mike
> .
>

John Spencer · External Since: Jul 15, 2009 Posts: 47

One way to do this would be to use a Custom VBA function.
Untested -- use at your own risk

Public Function fAddSpaces(strIN)
Dim strOut As String
Dim i As Long

If Len(Trim(strIN & "")) = 0 Then
fAddSpaces = strIN
Else
For i = 1 To Len(strIN)
strOut = strOut & Mid(strIN, i, 1) & Space(1)
Next i
'Add next 3 lines if you want to get rid of multiple spaces in a row
While strOut Like "* *"
strOut = Replace(strOut, " ", " ")
Wend
'Add next line if you want to trim leading or trailing spaces
strOut = Trim(strOut)

fAddSpaces = strOut
End If

As written the results should be:
=fAddSpaces(" ABC DEF ") returns "A B C D E F"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

mike UH wrote:
> I have a field called Customer Number with Text / alpha numeric data
> type, which varies in length. i need to insert a space between the
> characters.
>
> example
> 1234 becomes 1 2 3 4
> asdf123 becomes a s d f 1 2 3
>
> please help me on how to solve this problem.
>
> Thanks
> Mike

KenSheridan via AccessMon · External Since: May 16, 2009 Posts: 68

Mike:

The following function should do it:

Public Function PadString(strVal As String, strPadChr As String) As String

Dim intPads As Integer
Dim n As Integer

intPads = Len(strVal)

For n = 1 To intPads
PadString = PadString & strPadChr & Mid(strVal, n, 1)
Next n

' remove leading pad character
PadString = Mid(PadString, 2)

End Function

Add it to a standard module. If you simply want to display the values with
the intervening spaces call the function in the ControlSource of a text box
in a form or report, or in a computed column in a query. You can use any
character as the 'pad character' by passing it into the function, so to use
spaces:

=PadString([Customer Number]," ")

If you want to change the values in the column permanently call it in an
update query:

UPDATE [YourTable]
SET [Customer Number]
=PadString([Customer Number]," ");

I'm assuming Customer Number cannot be Null.

Ken Sheridan
Stafford, England

mike UH wrote:
>I have a field called Customer Number with Text / alpha numeric data
>type, which varies in length. i need to insert a space between the
>characters.
>
>example
>1234 becomes 1 2 3 4
>asdf123 becomes a s d f 1 2 3
>
>please help me on how to solve this problem.
>
>Thanks
>Mike

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200911/1