Results 1 to 3 of 3

Thread: Any Idears???

  1. #1
    Join Date
    Dec 2003

    Unanswered: Any Idears???

    Let me preface this thread with the simple phrase "Don't Ask, I know"

    The data is all jacked up and now I have been tasked to try and get some bits of it out.

    Here is an example of part of the string.
    *** MASTER *** June 8, 2001 at 12:14pm

    i need to pull the date with disregard to the time.

    My initial thoughts were
    1. do a instr function to find " *** "
    2. the do another instr to find " at "
    3. then do a mid between the two points
    4. then trim the string
    5. convert to date

    which wouldn't bet that bad, but we are talking 3000 rows.

    Public Function GetDate(SomeHorribleString As String) As Date
    Dim iStart As Integer, iEnd As Integer
    '+5 accounts for search string
    iStart = InStr(1, SomeHorribleString, " *** ") + 5 iEnd = InStr(iStart, SomeHorribleString, " at ")
    GetDate = CDate(mID(SomeHorribleString, iStart, iEnd - iStart))
    End Function
    Yes, I know the data sucks.

    Does anyone have any othe ideas that might be a better approach?

    Any ideas/suggestions are greatly appreciated.


    After searching through a couple of strings I noticed that there may be several notes in one memo field i.e.
    *** SEMPLOYEE (Some Employee) *** March 8, 2005 at 11:05am
    Changed from XXX to YYY. ID number switches from 000000 to 111111 per Another Employee.

    *** SEMPLOYEE (Some Employee) *** January 7, 2005 at 9:56am
    Added item 2 and item 1 designations per Sue facility visit contact sheet.

    *** AEMPLOYEE (Another Employee) *** January 17, 2001 at 9:30am
    Called to register for Conference call on January 17, 2001

    **End of Data Example**

    My initial thoughts are to create a recursive method that takes into consideration a start position.

    Again if anyone has any thoughts please share.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    In your "several notes in one memo field" example, am I to understand all of that exists in one field? If so, recursion is probably the way to go.

    The hard part here is figuring out what pattern to look for. The fact that it appears people can type just about whatever they damn-well feel like in the same field you're searching significantly complicates things. I would probably key on ") *** " Then take everything up to the next occurance of " at ".

    You haven't indicated which parts of the data you need to cull, the code you use to approach this will be strongly dictated by the desired result.

    edit: I tried looking at it sideways.. do you need everything from "*** (" up to "at"? If so, I'm sure something can be worked out.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2003

    What I did

    here is what I did.

    As each field would result in subsequent multiple entries into a DB I decided to store the data as a collection, then for each item in the collection I could do unique insert statement.

    First step was to create an object to hold some of the data. I prefer to work with these rather than create arrays of collections, which for me get's hard to remember which index refers to which item in the array
    Option Compare Database
    Option Explicit
    Dim tDate As Date
    Dim tNote As String
    Public Property Get NoteDate() As Date
        NoteDate = tDate
    End Property
    Public Property Let NoteDate(Value As Date)
        tDate = Value
    End Property
    Public Property Get NoteText() As String
        NoteText = tNote
    End Property
    Public Property Let NoteText(Value As String)
        tNote = Value
    End Property
    a rather rudimentary class, but makes management of the information a little easier

    with this in place we create the method to pull the data out. This method returns a note object and the end date for the note string to be inserted, which is used by the recursive method

    Public Function GetDate(SomeHorribleString As String, Optional StartPosition As Integer = 4) As Variant
    On Error GoTo ErrHandler
    Dim iStart As Integer, iEnd As Integer
    Dim ColInfo As New Collection
    Dim tNote As Note
        iStart = InStr(StartPosition, SomeHorribleString, " *** ") + 5
        iEnd = InStr(iStart, SomeHorribleString, " at ")
        Set tNote = New Note
        tNote.NoteDate = CDate(mID(SomeHorribleString, iStart, iEnd - iStart))
        iEnd = InStr(iEnd, SomeHorribleString, "*** ")
        If iEnd = 0 Then
            tNote.NoteText = mID(SomeHorribleString, StartPosition)
            tNote.NoteText = mID(SomeHorribleString, StartPosition, iEnd - StartPosition)
        End If
        ColInfo.Add tNote, "Note"
        ColInfo.Add iEnd, "End"
        Set GetDate = ColInfo
        Set tNote = Nothing
        Exit Function
        Set GetDate = Nothing
        Resume ExitHere
    End Function
    The second step was to create a recursive means by which to cycle through the entire string to find the next instances of a date and the note

    Public Function GetDates(HorribleString As String) As Collection
    Dim colDates As New Collection
    Dim tcol As Variant
    Dim tNote As Note
        Set tcol = GetDate(HorribleString)
        If tcol Is Nothing Then Exit Function
        Do Until tcol("End") = 0
            colDates.Add tcol.Item("note")
            If tcol Is Nothing Then Exit Function
            Set tcol = GetDate(HorribleString, tcol.Item("end"))
        colDates.Add tcol.Item("note")
    Set GetDates = colDates
    End Function
    i realized I solved my own problem, but am curious of anyone's thoughts on approach and also thought others might find the code useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts