Find time within a text string

[sistinus]

I have a database that tracks support requests that come in through two different IM clients MS Communicator and Brosix. Each user copies/pastes a conversation history into a memo field named IMLog. What I need to do is extract the first and last time stamp from the message. I can have the users enter these time stamps manually, but I would like to have as much of this automated as possible.

It's not much of an issue that they're from two different IM clients. Since one of them includes the date I can use an If statement with InStr and the Date function.

Here's a couple examples of the message logs I'm working with. This first one is MS Communicator. The main problem with the message log is that the time stamp, name and message run together. Here's an example of a pasted Communicator message.

4:37 PMSmith, Danielhave a cx with a philips go gear 
modelSA4025/37
 

 is it supported?


4:41 PMBrown, Jamesnot supported only the SA29xx and SA52xx


4:42 PMSmith, Danielthx


4:42 PMBrown, Jameswelcome

Here's a sample message log from Brosix.

ACS_Kmyles
5/24/2011 4:33:59 PM
Hi guys can anyone confirm if the Nokia C5 is a compatible device. Not listed in Device Center 





Jane Goodwin
5/24/2011 4:35:06 PM
No, it won't work. That device can't be activated.





ACS_Kmyles
5/24/2011 4:35:49 PM
K thanks






Jane Goodwin
5/24/2011 4:36:14 PM
You're welcome. Have a great shift!

I just want to be able to have Access know what time the conversations started and what time the conversations ended. I've tried messing with some vba code to get this info but I'm not that great at vba and it wasn't pretty. If anyone has any suggestions, tips or has done anything in the past like this please let me know. I appreciate any help you can give.

 

[hollifd]

Here is some very quick untested code that may help you. It searches for the first occurence of " PM" or " AM" to find the StartingTime and sets a flag to know that the starting time has been found. Next, It saves any additional occurences of " PM" or " AM" in the EndingTime. Hopefully, this will help you solve this problem. This example assumes the text to be in a text file in C:\Temp\junk2.txt but the text could be in a textbox on a form or a field or somewhere else. You will need to modify the code to pull the text from where ever it might be saved. For testing, just paste your text in a file on C:\temp\junk2.txt.

Public Sub GetStartEndTimes()

FirstTime = 0   'This is a flag for knowing if you have saved the start time
Open "C:\temp\junk2.txt" For Input As #1
While Not EOF(1)
    Line Input #1, Hold$
    
    Let Found = InStr(Hold$, " PM")
    If Found > 0 Then
        If FirstTime = 0 Then
            MyStartTime$ = Mid$(Hold$, 1, Found + 2)
            FirstTime = 1
        Else
            MyEndTime$ = Mid$(Hold$, 1, Found + 2)
        End If
    End If
    
    Let Found = InStr(Hold$, " AM")
    If Found > 0 Then
        If FirstTime = 0 Then
            MyStartTime$ = Mid$(Hold$, 1, Found + 2)
            FirstTime = 1
        Else
            MyEndTime$ = Mid$(Hold$, 1, Found + 2)
        End If
    End If
    
Wend
Close #1

MsgBox MyStartTime$ & vbCrLf & MyEndTime$

End Sub

 

[sistinus]

This works perfectly. Exactly what I needed. Thanks for the help and the quick response.