CODEWITHSUNDEEP
sqlvbams-access
sa-biene
sa-biene

Reputation: 13

MS Access Split -> Manipulate and merge string in query

I got the following type of input in a column and would like to get the output below

Input:

01212/001231412/0123123
04654/000009846/9015684

Output:

01212/1231412/0123123
04654/9846/9015684

So I need to remove leading zeros in the middle part. My assumption is that

Is this possible in Access via SQL or do I need to write a VBA script? How do I so the 2nd part, detecting leading zeros automatically?

Upvotes: 0

Views: 167

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

This will work:

  Sub Rzeros()
  
     Dim strInString      As String
     Dim strOutString     As String
     Dim buf              As Variant
     
     strInString = "01212/001231412/0123123 04654/000009846/9015684"
     
     buf = Split(strInString, "/")
     buf(1) = Clng(buf(1))
     buf(3) = CLng(buf(3))
     
     strOutString = Join(buf, "/")
     
     Debug.Print strOutString
     
  End Sub

OutPut:
01212/1231412/0123123 04654/9846/9015684

To use this in a query, or report, or any expression? Then use a function like this:

Public Function Znum(v As Variant) As String

  If IsNull(v) Then Exit Function
  
  Dim buf        As Variant
  buf = Split(v, "/")
  buf(1) = CLng(buf(1))
  buf(3) = CLng(buf(3))
  
  Znum = Join(buf, "/")
  
End Function

So, now in a report, or form you can use this expression for the control souce:

=(Znum([My column name]))

Or, in a sql query, you can do this:

SELECT ID,  FunnyNumber, Znum([FunnyNumber]) AS Betternum from MyTable

Upvotes: 0

Applecore
Applecore

Reputation: 4099

You can indeed use a VBA procedure to do this. My example is below:

Function fRemoveZero(strData As String) As String
    Dim aData() As String
    Dim intLoop1 As Integer
    aData = Split(strData, "/")
    If LBound(aData) = UBound(aData) Then
        fRemoveZero = strData
    Else
        For intLoop1 = LBound(aData) + 1 To UBound(aData)
            If IsNumeric(aData(intLoop1)) Then aData(intLoop1) = CLng(aData(intLoop1))
        Next intLoop1
        fRemoveZero = Join(aData, "/")
    End If
End Function

When run on the sample:

?fRemoveZero("01212/001231412/0123123 04654/000009846/9015684")
01212/1231412/0123123 04654/9846/9015684

Regards,

Upvotes: 1

Related Questions