Expanding text string

[KevinG]

I have a column of text strings in a table that I need to expand. An example of one of the strings is C20-21,C30-35,C44. This needs to be expanded to C20,C21,C30,C31,C32,C33,C34,C35,C44. I would appreciate any help that you can give me on what needs to be done to expand the strings of text. Thanks -Kevin

[rpeare]

The only thing I can think to do is to create a temporary table and use VBA to cycle through the items, is there a reason this data is not normalized to start with? And are you open to a vba/temp table solution? IS the data in this text field ALWAYS going to follow the same format where there are a static number of text characters before the number fields, are the hyphens always going to be without spaces? Or can you have variations like:

C20-21,C30-35,C44
C20 - 21,C30 - 35,C44
C 20-21,C 30-35,C 44
C 20 - 21, C 30 - 35, C 44
C20- 21, C 30-35, C 44

That's the problem with text fields instead of a normalized structure, parsing them becomes tedious and difficult if the formatting is not the same every time.

[KevinG]

This data is imported from another program and this is the format it uses to compress the data. These are reference designators for a PCB. Yes I am open to VBA. Each line in the table will not look exactly like the example. Some lines may only have one designator (C2), others may not have a hyphen (C14,C) and some may only have a hyphen (C44-C50). They will always start with a letter, but not always a C. There will not be any extra spaces in the string. If there are I could strip out the spaces first before the string is expanded.

[rpeare]

Ok so let's say your imported data just has one field I'd create an additional field in my table to store the 'final' string

tblTest
ImportString       FinalString
C20-21,C30-35,C44

Then run code like this:

Dim db As Database
Dim rst As Recordset
Dim sString As String
Dim aLineArray
Dim sArrayItem As String
Dim sPrefix As String
Dim sSuffix As String
Dim iMin As Integer
Dim iMax As Integer
Dim sFinalString As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ImportString, FinalString FROM tblTest WHERE (FinalString) Is Null")

If rst.RecordCount <> 0 Then
    Do While rst.EOF <> True
        'resets the finalstring
        sFinalString = ""
        'removes any spaces in string
        If Len(Trim(rst!importstring)) > 0 Then
            sString = Replace(rst!importstring, " ", "")
        Else
            sString = ""
        End If
        'if the string length is greater than 0
        If Len(sString) > 0 Then
            'split the string into an array of items delimited by a comma
            aLineArray = Split(sString, ",")
                'for each item in the array
                For a = 0 To UBound(aLineArray)
                    'starting array string
                    sArrayItem = aLineArray(a)
                    sPrefix = Left(sArrayItem, 1)
                    'finds the min and max range of any string in the array
                    If InStr(sArrayItem, "-") > 0 Then
                        iMin = Mid(sArrayItem, 2, InStr(sArrayItem, "-") - 2)
                        iMax = Right(sArrayItem, Len(sArrayItem) - InStr(sArrayItem, "-"))
                    Else
                        iMin = Right(sArrayItem, Len(sArrayItem) - 1)
                        iMax = iMin
                    End If
                    'cycles through the values within each segment to create a final string
                    For iMin = iMin To iMax
                        sFinalString = sFinalString & sPrefix & iMin & ","
                    Next iMin
                    'Debug.Print "    " & iMin & "  " & iMax
                Next a
            sFinalString = Left(sFinalString, Len(sFinalString) - 1)
            'updates the table with the final string
            rst.Edit
            rst!finalstring = sFinalString
            rst.Update
            rst.MoveNext
        Else
            rst.MoveNext
        End If
    Loop
End If

[rpeare]

This was my test Data (I had a null value record in there):

ImportString
C20-21,C30-35,C44
D 20- 23,D 30-32,D 46
E20-24
F1
G1-8,H1-5

[KevinG]

Wow that was great! While running this on some test strings I noticed there is one more possibility. Instead of C30-35 another valid format is C30-C35. Is this an easy modification to support this format also?

[rpeare]

'easy' is a relative term

What you'd have to do is test the string after the - to see if it's numeric. If it is the code works just fine, if it's not, you'd have to lop off that first character to get the numeric portion.

look at the 'isnumeric' function to test the string.

[KevinG]

OK I have everything working perfectly in a simple database. In the simple database I have the same table names and field names as my final database. When I copy the code to the final database I get a compile error: Method or data member not found on rst.Edit. If I comment that line out then I get a run time error 13: type mismatch when I step over set rst=db.OpenRecordset...

Any ideas what I am doing wrong?

[rpeare]

When you look at the code in the version that works, look at the references (TOOLS>REFERENCES). Make sure the same list of references exist in your 'live' database. The reference I was using for this was Microsoft DAO 3.6. If you're using an ADO reference you'll have to alter the code to use the syntax your database is expecting, I don't believe you can use both DAO and ADO in the same database.

[KevinG]

Yes that was the problem. Thanks for the help. I need to modify it to accept more than one letter for the prefix but I have an idea what I need to do. Thanks again.