CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
Jeff
Jeff

Reputation: 41

How do I split up a year range into individual years in a given field?

I was working in Access to make a query of a few tables, and realized that a column of a table does not meet a specific requirement.

I have a field that consists of thousands of records, and it contains "years" in the following format (an example) : 1915-1918.

What I want to accomplish is to make that value individual in the records. So the end result would be : 1915,1916,1917,1918.

Basically, I want to convert 1915-1918 to 1915,1916,1917,1918.

I thought a simple concatenation would suffice, but could not wrap my head around how to make it so that it can do it for all thousands of records. I did some research and reached the conclusion that a user defined function might be the way to go. How would I go about this?

Upvotes: 2

Views: 687

Answers (4)

HansUp
HansUp

Reputation: 97101

When your field value consists of 4 digits followed by a dash followed by 4 more digits, this function returns a comma-separated list of years.

In any other cases (Null, a single year such as "1915" instead of a year range, or anything else), the function returns the starting value.

Public Function YearList(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
    YearList = varReturn
End Function

However, this approach assumes the start year in each range will be less than the end year. In other words, you would need to invest more effort to make YearList("1915-1912") return a list of years instead of an empty string.

If that function returns what you want, you could use it in a SELECT query.

SELECT years_field, YearList(years_field)
FROM YourTable;

Or if you want to replace the stored values in your years field, you can use the function in an UPDATE query.

UPDATE YourTable
SET years_field = YearList(years_field);

Upvotes: 1

tbur
tbur

Reputation: 2454

Add the code below to a module in Visual Basic

Public Function CommaDates(Start_End) As String
    Dim strt As String
    Dim endd As String
    Dim x As Long

    strt = Left(Start_End, 4)
    endd = Right(Start_End, 4)
    CommaDates = strt

    For x = strt + 1 To endd
        CommaDates = CommaDates & "," & x
    Next x
End Function

Call this in a query like NEW_DATE: CommaDates([OLD_DATE_FIELD_NAME])

Upvotes: 0

Steve S
Steve S

Reputation: 431

You can use the Split function to return an array from the "years" field that contains the upper and lower year. Then loop from the lower year to the upper year and build the concatenated string. For example:

Public Function SplitYears(Years As String) As String
    Dim v As Variant
    Dim i As Long
    Dim s As String

    v = Split(Years, "-", 2)
    If UBound(v) = 1 Then
        For i = v(0) To v(1)
            s = s & "," & CStr(i)
        Next i
        s = Right(s, Len(s) - 1)
    Else
        s = v(0)
    End If
    SplitYears = s

End Function

Upvotes: 1

bf2020
bf2020

Reputation: 732

In Excel, make a sequential reference table of Years, for the range of years that you expect to encounter.

Next, use left and right functions to get the start and end of the range.

Create an update query and update the target field with a concatenation of target field to itself and then also the reference year values between that also fit between the start and end of the range.

Or I guess you could make a user function.

Upvotes: 0

Related Questions