CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
Jeff
Jeff

Reputation: 41

Converting list of years to double digits

I have a table with a few hundred products on Access and I need to convert the years from the following format

1992,1993,1994,1995

to 92-95

A while back I learned from the forums how convert my years from 1992-1995 to how the format is now. But with recent requirements I now need another field with the first and last year from the field and double digits only. I was working with the function created a few weeks ago here but there are some parts I could not understand and came to the conclusion I can't use it for this case. Also some years are single such as "1984" which should show up like "84-84".

I'm not sure the previous function is usable but I'll add it in.

Public Function Conversion(ByVal pInput As Variant) As Variant
    Dim astrPieces() As String
    Dim i As Long
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim varReturn As Variant

    If pInput Like "####-####" Then
        astrPieces = Split(pInput, "-")
        lngFirst = CLng(astrPieces(0))
        lngLast = CLng(astrPieces(1))
        For i = lngFirst To lngLast
            varReturn = varReturn & "," & CStr(i)
        Next
        If Len(varReturn) > 0 Then
            varReturn = Mid(varReturn, 2)
        End If
    Else
        varReturn = pInput
    End If
    Conversion = varReturn
End Function

Upvotes: 0

Views: 41

Answers (1)

Alex K.
Alex K.

Reputation: 175796

Assuming they are in order this would word for multiple or single years;

Public Function ToShortYearSpan(ByVal pInput As Variant) As Variant
   ToShortYearSpan = Mid$(pInput, 3, 2) & "-" & Right$(pInput, 2)
End Function

Upvotes: 2

Related Questions