Complicated Split

[Keith]

Mile-O, I can sort out the adding the missing part of the year once I have split the string. My problem is the insertSQL statement which is where I am getting the error. The tblSandboxTo in my previous post is not the result of the code split but one that I made as an example of the result I want. The year spans the1900's & 2000's, but thats another day.

 

[BlueIshDan]

What is the error that you are getting?

 

[Keith]

Syntax error in INSERT INTO statement. I think that I need to capture the array values and put the insert SQL after Next i and before rs.MoveNext

 

[BlueIshDan]

Right, you're not taking into the account that some of your second fields have more or less values than others.

At some point you'll be looking for indexes of your array that are not there.

Have you tried the air code I typed out for you?

 

[Keith]

BluIshDan, I get a function Not Defined Error highlighting split_boats

 

[vbaInet]

You're probably thinking too hard. I've extracted the different combination of records from what you've already posted:

MemberID	Boats
1001	Churchill,Warspite,Spartan,Sceptre,Superb
1024	H43 (42) H44 (42) L26 (42) Tactician(42-44) Varangian(45) Talent (45-46) Tally-Ho(46-47)
1067	Truculent H28 Truant Templar Amphion Alliance Astute Trespasser
1003	Artful

... inserting into the table should be the last thing on your mind. First get the correct split of all different combinations.

I've put the above in order as well, so your test should be:
1. Split(",") - if Ubound() is greater than 0 proceed to insert, otherwise
2. Split(" ") - if UBound() is greater than 0 proceed to insert

Next step, the Insert part:
3. Loop through each item in the array and do a LIKE check on each item
4. LIKE "*(00*", if successful extract boat name, extract the date part(s) and insert entire record, otherwise
5. Just insert the item, which is just the boat name

If there are more combinations, let us know.

 

[vbaInet]

Now I see a problem with line 1024. Are the spaces actually inconsistent like that?
I.e. H43 (42) and Tactician(42-44)

 

[Keith]

vbaInet, I have seperated all the records with no dates an have sucessfuly split them into the destination table. I am now left with those with dates. I have converted those records into comma seperated Field. I am having trouble splitting those record that only have one date My code works if there are two dates, I'm running query to add ,?? to those with only one date. Once split it should be easy to remove the ?marks. I hope.

 

[vbaInet]

I am now left with those with dates.

Please look at the steps I mentioned. They are written in a logical order which should cover all basis.
Yes your code works for some combinations but it doesn't work for all.

And also need an answer to post #27

 

[BlueIshDan]

You could run a replace("(", " (") and then replace ("[2 spaces](", " (")

 

[vbaInet]

Actually what you want to do is replace " (" with "(" so that the Split(" ") covers both 1024 and 1067 for the first stage.

But we don't know yet if that's just a typo or if that's real data. I guess we'll wait for Keith's reply on this.

 

[BlueIshDan]

Right! Sorry

 

[vbaInet]

This one splits the boats into boat, start and end dates:

Private Type Rec
    MemberID    As Long
    Boat        As String
    StartDate   As Variant
    EndDate     As Variant
End Type

Public Sub SplitBoats(ByVal StrBoats As String, _
                      ByRef Record As Rec)
    
    Dim varBoats    As Variant
    Dim varRecord   As Variant
    Dim varItem     As Variant
    Dim strBoat     As String
    Dim varStart    As Variant
    Dim varEnd      As Variant
    Dim varDates    As Variant
    
    ' Clean up
    StrBoats = Replace(StrBoats, " (", "(")
    StrBoats = Replace(StrBoats, " ,", ",")
    
    ' first attempt
    varBoats = Split(StrBoats, ", ")
    
    If UBound(varBoats) = 0 Then
        ' second attempt
        varBoats = Split(varBoats(0), " ")
    End If
    
    ' final check to get individual values and call to INSERT
    For Each varItem In varBoats
        If varItem Like "*(##*" Then
            varRecord = Split(varItem, "(")
            Record.Boat = varRecord(0)
            
            ' tidy up and get date(s)
            varRecord(1) = Replace(varRecord(1), ")", "")
            varDates = Split(varRecord(1), "-")
            
            Record.StartDate = varDates(0)
            If UBound(varDates) > 0 Then
                Record.EndDate = varDates(1)
            End If
        Else
            Record.Boat = varItem
        End If
        
        ' pass Record to a function that builds the SQL string and INSERTs
        Call InsertRecord(Record)
        
        ' reset variables for next run
        With Record
            .Boat = vbNullString
            .StartDate = Empty
            .EndDate = Empty
        End With
    Next
End Sub

Call this function for each Member ID run and remember to dim a variable of type Rec and set the MemberID before passing it to the function.

 

[Keith]

Job done, Thanks for everybodies help.