Manipulating Data

[mous]

Access 2000

I have a table with a text field (56 characters) containing a string of A/B's.

Example,

Unique Ref : AABBBBAA

Each of the characters represent 1 week, A = week 1 01/01/2003, A = week 2, 08/01/2003, B = Week 3, 15/01/2003 etc etc.

Where the character is A then this is true where it is B it is false.

Basically, I want to make another table containing several rows based on the above. For example,

My new table should read from the above set of data

Uniq Ref: Start Week End Week

1 1 2
2 3 6
3 7 9

Is this possible at all.

Many Thanks

NB. There is no way that I can alter this table structure.

 

[namliam]

doable offcourse....

Sub test()
    Dim someString As String
    Dim myPrevChar As String
    Dim myThisChar As String
    Dim myStartNo As Integer
    Dim myNumber As Integer
    Dim I As Integer
    someString = "AABBBBAAABB"
    myPrevChar = ""
    myStartNo = 1
    myPrevChar = Left$(someString, 1)
    myNumber = 1
    Debug.Print "Number", "StartWk", "EndWk"
    For I = 1 To Len(someString)
        myThisChar = Mid$(someString, I, 1)
        If myPrevChar <> myThisChar Then
            Debug.Print myNumber, myStartNo, I
            myNumber = myNumber + 1
            myStartNo = I + 1
        End If
        myPrevChar = myThisChar
    Next I
End Sub

A bit Q&D but it works i think, above code gives below output in the immediat window....

Number        StartWk       EndWk
 1             1             3
 2             4             7
 3             8             10

Regards

The Mailman

 

[dcx693]

Yes, this is possible, but I was looking at your sample of how you'd like the results to appear, and I'm not sure how you can tell from that which of the weeks is true and which is false. Unless you don't care about that.

 

[mous]

Yes, sorry I only want to make my new table if the characters are true i.e. A.

I'm still a bit new to coding, so how exactley would I run this code to create a new table.

Thanks