Public Function Add6(ByVal pvWord)
Dim i As Byte, j As Byte
Dim vNew, vChr
j = 1
For i = 1 To Len(pvWord)
vChr = Mid(pvWord, i, 1)
If j = 7 Then
vNew = vNew & " "
j = 1
End If
vNew = vNew & vChr
j = j + 1
Next
Add6 = vNew
End Function
that is absolutely perfect! thanks so much
Public Function Add6(ByVal pvWord)
Dim i As Byte, j As Byte
Dim vNew, vChr
j = 1
For i = 1 To Len(pvWord)
vChr = Mid(pvWord, i, 1)
If j = 7 Then
vNew = vNew & " "
j = 1
End If
vNew = vNew & vChr
j = j + 1
Next
Add6 = vNew
End Function
Public Function InsertSpace(ByVal psWord As String, ByVal GroupChars As Integer, Optional ByVal NumSpaces As Integer = 1) As String
Dim i As Integer
Dim sChr As String
Dim sNew As String
i = 1
Do
sChr = Mid(psWord, i, GroupChars)
If Len(sChr) = 0 Then Exit Do
sNew = sNew & sChr & Space(NumSpaces)
i = i + GroupChars
Loop
InsertSpace = RTrim(sNew)
End Function
simple Update query will do:
Update table1
Set fld=Iif(Len(Trim(fld & ""))<6, fld, Left(fld, 5) & " " & Mid( fld, 6))
SELECT tblTest.TestString, InsertSpace([testString],6,1) AS Galaxiom, IIf(Len(Trim([TestString] & ""))<6,[TestString],Left([TestString],5) & " " & Mid([TestString],6)) AS Arnelgp
FROM tblTest;
How it is wrong? When you add space on the 6 char the 6 char will be the space and the 6 char and the rest rest of text will shift to 7..
it could I think be generalised further by adding a further optional argument for the character to be added and setting a space as the default.
However, your method does at least handle nulls which the other methods don't .... unless also enclosed in Nz(...,"")
I think that always needs a few caveats.Expressions not using custom functions should always be preferred.
Yes I thought that too. Would be good for dashes in phone numbers.
This made me realise it is really a formatting task and wondered how ajb was using it in their app.