0

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

  • I need to split the string
  • detect if there are leading zeros in the 2nd part
  • merge everything together

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
sa-biene
  • 13
  • 7
  • would removing all leading zeros be ok, or do you want this just for the 2nd to last number as you have? – Albert D. Kallal Apr 24 '21 at 19:29
  • 1
    What reasoning is there in replacing leading zeroes? "001231412" and "000009846" both lose the leading 0s, but "0123123 04654" doesn't. – Applecore Apr 24 '21 at 19:47
  • Does this answer your question? [Ms Access Query: Concatenating Rows through a query](https://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query) – Monica Aspiras Labbao Apr 25 '21 at 06:53
  • @Albert and Applecore: I need to remove the leading zeros only in the 2nd part, due to a transfer into another system. – sa-biene Apr 26 '21 at 13:58
  • Well, as the code shows, you can pick 0 to any number - the last edit shows converting 1 and 3 (it zero based) so that would convert 1 and 4 to numbers and remove the zero's. If you only want the 2nd element removed - then just ust buf(1) = clng(buf(1) and you good to go. As noted you can even use the function in a sql query - and you could then create a append query to a new table, or even export directly against that query in place of a table. Not clear how or why your example says 4th element for removing leading zeros - but code allows you to pick whichever element to convert anyway. – Albert D. Kallal Apr 26 '21 at 15:32

2 Answers2

1

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,

Applecore
  • 3,934
  • 2
  • 9
  • 13
0

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
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • This won't work according to the example given - the second element, "001231412", needs to lose the leading 0s as well. – Applecore Apr 24 '21 at 19:57
  • Just add the extra same trick against the 2nd to last one. I'll edit - it is a trivial change. I mean, the idea and concept here should be clear. – Albert D. Kallal Apr 24 '21 at 20:10
  • see my edit - it will now work - you just do the same thing against then 2nd element - and off you go! I see LITTLE reason to use a loop as you have done - but no big worries. – Albert D. Kallal Apr 24 '21 at 20:11
  • I'm using a loop just in case there are a variable number of elements..... – Applecore Apr 24 '21 at 20:14
  • Sure - that's fair. The problem does suggest that this conversion is to only occur for 2nd number, and 2nd to last. So, it not clear how such a solution could work against a variable number of elements when in fact the 2 elements slated for conversion are to be hard coded? As noted, we both asked for more information - is it all numbers, or just the 2nd and 2nd to last? If all numbers? Then I will up-vote your answer! – Albert D. Kallal Apr 24 '21 at 20:16