Select Case? Not sure how to do this?

[noputz]

I am trying to do either an update or append from one Access Table to another.
I want to clean the data up and get the correct information in the corresonding columns.
I am having a hard time figuring out how to do this. Any help will be appreciated. Below is what the
original table looks like. And then how I would like there results Table. I am using Access 2010.
Table: StoreData
CoName Hours1 Hours2 Hours3
J&H Sun 11:00AM - 4:00PM Mon 10:30AM - 5:30PM Tue 10:30AM - 5:30PM
L&M Mon 7:00AM - 5:30PM Tue 7:00AM - 5:30PM Wed 7:00AM - 5:30PM
J&B Mon 8:00AM - 6:00PM Tue 8:00AM - 6:00PM Thu 8:00AM - 6:00PM


Results
Table: CleanData
CoName MonStart MonEnd TuesStar TuesEnd WedStart WedEnd
J&H 10:30 5:30 10:30 5:30 10:30 7:00
L&M 7:00 5:30 7:00 5:30 7:00 5:30
J&B 8:00 6:00 8:00 6:00 8:00 6:00

 

[KenHigg]

You should be able to do it with a series of string functions like left(), right() and mid().

 

[nschroeder]

I discovered the Split function on this forum a few weeks ago, and it would be perfect for your needs. Remember that the TimVal array it produces is zero-based. I haven't tested this code, but it should be close. Use the debugger to tweak and check results. Have fun!

Sub GetTimeVals
Dim db As Database
Dim rsStoreData as Recordset
Dim rsCleanData as Recordset
Dim TimeVal as Variant
 
    Set db = CurrentDb()
    Set rsStoreData = db.OpenRecordset("StoreData", dbOpenTable)
    Set rsCleanData = db.OpenRecordset("CleanData", dbOpenTable)

    With rsCleanData
        Do Until rsStoreData.EOF
            .AddNew
            !CoName = rsStoreData.CoName

            TimeVal = Split(rsStoreData!Hours1, " ") ' Put space-delimited Hours string into array
            !MonStart = TimeVal(1)
            !MonEnd = TimeVal(3)

            TimeVal = Split(rsStoreData!Hours2, " ") 
            !TueStart = TimeVal(1)
            !TueEnd = TimeVal(3)

            TimeVal = Split(rsStoreData!Hours3, " ") 
            !WedStart = TimeVal(1)
            !WedEnd = TimeVal(3)

            .Update
            rsStoreData.MoveNext
        Loop
    End With

    Set rsStoreData = Nothing
    Set rsCleanData = Nothing
    Set db = Nothing