CODEWITHSUNDEEP
ms-accessvbams-access-2010
Shawn
Shawn

Reputation: 1

I want to learn how to convert a string using access 2010 vba

I want to go from this type string 

4567 0212 11 4567 0212 12 4567 0212 13 4567 0212 14 4567 0212 15

to one that looks like this 

4567 0212 11, 4567 0212 12, 4567 0212 13, 4567 0212 14 and 4567 0212 15

This will be used in an automated outlook message, so the length will change with the number of records I am pulling.

Upvotes: 0

Views: 101

Answers (2)

HansUp
HansUp

Reputation: 97100

Here is an Access 2010 Immediate window session demonstrating a custom VBA function which performs your requested string conversion.

? ConvertString("4567 0212 11 4567 0212 12 4567 0212 13 4567 0212 14 4567 0212 15")
4567 0212 11, 4567 0212 12, 4567 0212 13, 4567 0212 14 and 4567 0212 15

The function is "quick & dirty"; it works correctly for your sample string. However any input which does not consist of multiples of 3 sets of characters separated by spaces may trigger an error or not return the string you want. But hopefully this will get you started.

Public Function ConvertString(ByVal pInput As String) As String
    Dim astrPieces() As String
    Dim strOutput As String
    Dim i As Long
    Dim lngUBound As Long

    astrPieces = Split(pInput)
    lngUBound = UBound(astrPieces)
    i = 0
    Do While i < lngUBound
        If (i + 2) < lngUBound Then
            strOutput = strOutput & ", "
        Else
            If i = 0 Then
                strOutput = strOutput & ", "
            Else
                strOutput = strOutput & " and "
            End If
        End If
        strOutput = strOutput & astrPieces(i) & " " & astrPieces(i + 1) & _
            " " & astrPieces(i + 2)
        i = i + 3
    Loop
    If Len(strOutput) > 0 Then
        ' discard leading ", "
        strOutput = Mid(strOutput, 3)
    End If
    ConvertString = strOutput
End Function

Upvotes: 1

Maciej Los
Maciej Los

Reputation: 8591

As per my understanding, you wanted to add comma every 12 digits, so test it:

Sub Test()
Dim sText As String

sText = "4567 0212 11 4567 0212 12 4567 0212 13 4567 0212 14 4567 0212 15"
MsgBox "New string is:" & vbCr & ChangeString(sText)



End Sub

Function ChangeString(sInput As String) As String

Dim i As Integer, sTmp As String

If Len(sInput) < 13 Then Exit Function

For i = 1 To Len(sInput) Step 13
    sTmp = sTmp & Mid(sInput, i, 12) & ", "
Next

ChangeString = sTmp

End Function

Result:

4567 0212 11, 4567 0212 12, 4567 0212 13, 4567 0212 14, 4567 0212 15,

Upvotes: 0

Related Questions