Help extracting dates

[Ammarhm]

Hi
I have a variable (hosp) containing a text that looks like this:

Admission date: 31aug2004 Reason: xxxxxxxxx
Discharge date: 16oct2004 xxxxxxxx
Admission date: 28oct2004 Reason: xxxxxxxxxxxxx
Discharge date: 09nov2004 Discharged xxxxxxxxx
Admission date: 09nov2004 Reason: xxxxxx
Discharge date: 05dec2004 Discharged xxxxxxx
Admission date: 05dec2004 Reason: xxxxx
Discharge date: 31dec2004

And then another variable (evt) that contains a text like "12dec2004"

So everything is stored as pure "text" in those variable
I need do write a code that would identify the dates in the variable (hosp) and then looks for the hospitalization period when the even occured, ie Admission date =< evt date =< discharge date
How can you do that please?
Regards

 

[John Big Booty]

The following code will extract the date from your strings;

    Dim yrInt As Integer
    Dim mthInt As Integer
    Dim dayInt As Integer
    dim rsltDt as Date
    
    dayInt = Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 2, 2)
    Select Case Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 4, 3)
        Case "jan"
        mthInt = 1
        Case "feb"
        mthInt = 2
        Case "mar"
        mthInt = 3
        Case "apr"
        mthInt = 4
        Case "may"
        mthInt = 5
        Case "jun"
        mthInt = 6
        Case "jul"
        mthInt = 7
        Case "aug"
        mthInt = 8
        Case "sep"
        mthInt = 9
        Case "oct"
        mthInt = 10
        Case "nov"
        mthInt = 11
        Case "dec"
        mthInt = 12
    End Select
        
    yrInt = Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 7, 4)
    
    rsltDt  = DateSerial(yrInt, mthInt, dayInt)

This will only work however so long as your string are ALWAYS in the format as presented in your OP.

 

[Ammarhm]

The following code will extract the date from your strings;

    Dim yrInt As Integer
    Dim mthInt As Integer
    Dim dayInt As Integer
    dim rsltDt as Date
    
    dayInt = Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 2, 2)
    Select Case Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 4, 3)
        Case "jan"
        mthInt = 1
        Case "feb"
        mthInt = 2
        Case "mar"
        mthInt = 3
        Case "apr"
        mthInt = 4
        Case "may"
        mthInt = 5
        Case "jun"
        mthInt = 6
        Case "jul"
        mthInt = 7
        Case "aug"
        mthInt = 8
        Case "sep"
        mthInt = 9
        Case "oct"
        mthInt = 10
        Case "nov"
        mthInt = 11
        Case "dec"
        mthInt = 12
    End Select
        
    yrInt = Mid(Me.YourTextField, InStr(1, Me.YourTextField, ":") + 7, 4)
    
    rsltDt  = DateSerial(yrInt, mthInt, dayInt)

This will only work however so long as your string are ALWAYS in the format as presented in your OP.

Thank you
My string will always be in the format i presented
The problem is that the inStr will always return the "first instance" of occurence, how would you loop this code to extract all dates and not only the first?
Regards

 

[John Big Booty]

What would your typical full string look like? Would it be consistent?

 

[Ammarhm]

What would your typical full string look like? Would it be consistent?

It would always have "Admission date:" and Dischage date:"
However it could contain only one admition and discharge or 20 of each..
Regards

 

[John Big Booty]

The following code will loop through your string and extract the dates;

Dim yrInt As Integer
    Dim mthInt As Integer
    Dim dayInt As Integer
    Dim rsltDt As Date
    
    Me.Text5 = Me.Text3 [COLOR="SeaGreen"]'Text3 is the control that contains your string
                                 'and Text5 is an unbound text box on the form 
                                 'that the string is transferred to so it can be manipulated.[/COLOR]
    
    While InStr(Me.Text5, "date:") <> 0
    
    dayInt = Mid(Me.Text5, InStr(1, Me.Text5, "date:") + 6, 2)
    Select Case Mid(Me.Text5, InStr(1, Me.Text5, "date:") + 8, 3)
        Case "jan"
        mthInt = 1
        Case "feb"
        mthInt = 2
        Case "mar"
        mthInt = 3
        Case "apr"
        mthInt = 4
        Case "may"
        mthInt = 5
        Case "jun"
        mthInt = 6
        Case "jul"
        mthInt = 7
        Case "aug"
        mthInt = 8
        Case "sep"
        mthInt = 9
        Case "oct"
        mthInt = 10
        Case "nov"
        mthInt = 11
        Case "dec"
        mthInt = 12
    End Select
        
    yrInt = Mid(Me.Text5, InStr(1, Me.Text5, "date:") + 11, 4)
    
    Me.Text8 = Me.Text8 & DateSerial(yrInt, mthInt, dayInt) & "; "  [COLOR="SeaGreen"]'Text 8 is another unbound text box into which the results are appended
                                                                                         'but you could put the results wherever you like.[/COLOR]
    
    Me.Text5 = Replace(Me.Text5, "date:", "@@@@@", 1, 1)
    
    Wend

Once again this relies on the consistency of your sting always being in the format Date: ##XXX## ie. the text date followed by a full colon a space two digits three letters (conforming to the month abbreviations in the Case statement) and two digits.