Solved Can a date field pull a date from the last line of a text field?

[gojets1721]

Full disclosure; I fully expect that this is not possible but I thought I'd atleast ask. I inherited a database of customer complaints and the user before me never tracked in its own field the date of when the complaint was closed out (i.e. customer contacted and remedy found). Instead, they had a "resolution" field where the user puts in their notes and the last line in this field always included the date that the complaint was closed (i.e. "10/29/2021: Complaint closed").

See my example DB for more info. Is there a way for access to take the date in the last line of each complaint and drop it into the "date closed" field? Each complaint is consistent in that every note in the resolution section starts with "date:". This DB has thousands of complaints so before manually adding each date, I thought I'd check if anyone has any ideas.

Thanks!

 

[theDBguy]

Hi. I don't see the pattern you mentioned in the sample database you posted. I won't be able to test any potential solution without it.

 

[Cronk]

You could use an update query

update tblComplaints set DateClosed = left([Resolution],instr([Resolution],":")-1)

 

[theDBguy]

Hi. I don't see the pattern you mentioned in the sample database you posted. I won't be able to test any potential solution without it.

@templeowls, given what you said in your original post, this is what I came up with. Hope it helps...

Public Function GetCloseDate(Resolution As String) As Variant
'thedbguy@gmail.com
'10/29/2021
'returns the closed date for a complaint

Dim regex As Object
Dim regmatch As Object
Dim strDateClosed As String
Dim var As Variant

Set regex = CreateObject("VBScript.RegExp")

With regex
    .Global = True
    .ignorecase = True
    .pattern = "\d{1,2}/\d{1,2}/\d{2,4}:Complaint closed"
    Set regmatch = .Execute(Resolution)
    For Each var In regmatch
        strDateClosed = Left(var, InStr(var, ":") - 1)
    Next
End With

If strDateClosed = "" Then
    GetCloseDate = Null
Else
    GetCloseDate = CDate(strDateClosed)
End If

Set regmatch = Nothing
Set regex = Nothing

End Function

PS. The above is based on my previous work here.
Code Snippets (accessmvp.com)

 

[The_Doc_Man]

The problem will be finding the date string. Once you can find it, the rest is easy. TheDBguy's regular expression would work if your pattern was reliable, i.e. ALWAYS with the dd/mm/yyyy :Complaint Closed string near the end of the text field. Try to verify this fact. If the pattern is not so predictable, you will have some trouble.

 

[jdraw]

I think Cronk's or theDBguy's suggestion will do the job provided the Resolution field is populated and has consistent structure.

An alternate query to ensure the last entry in Resolution is used:

update tblcomplaints
set DateClosed = mid(resolution,InStrRev([Resolution],":")-9,9);

 

[gojets1721]

The issue with

@templeowls, given what you said in your original post, this is what I came up with. Hope it helps...

Public Function GetCloseDate(Resolution As String) As Variant
'thedbguy@gmail.com
'10/29/2021
'returns the closed date for a complaint

Dim regex As Object
Dim regmatch As Object
Dim strDateClosed As String
Dim var As Variant

Set regex = CreateObject("VBScript.RegExp")

With regex
    .Global = True
    .ignorecase = True
    .pattern = "\d{1,2}/\d{1,2}/\d{2,4}:Complaint closed"
    Set regmatch = .Execute(Resolution)
    For Each var In regmatch
        strDateClosed = Left(var, InStr(var, ":") - 1)
    Next
End With

If strDateClosed = "" Then
    GetCloseDate = Null
Else
    GetCloseDate = CDate(strDateClosed)
End If

Set regmatch = Nothing
Set regex = Nothing

End Function

PS. The above is based on my previous work here.
Code Snippets (accessmvp.com)

The issue is that it's not always "date: complaint closed". Since its just a text field, the user was inconsistent with their notes when closing it out. They stated that the complaint was closed in a lot of different ways (i.e. "complaint closed", "complaint completed", "no further action", etc.). Sorry for not clarifying this point.

The only consistent part of it is that its always the last line in the text field and the line always has a date in the format of "mm/dd/yyyy: notes"

Is it possible to code it in a way that it pulls the first date on the last line into its own field?

 

[theDBguy]

The issue with

The issue is that it's not always "date: complaint closed". Since its just a text field, the user was inconsistent with their notes when closing it out. They stated that the complaint was closed in a lot of different ways (i.e. "complaint closed", "complaint completed", "no further action", etc.). Sorry for not clarifying this point.

The only consistent part of it is that its always the last line in the text field and the line always has a date in the format of "mm/dd/yyyy: notes"

Is it possible to code it in a way that it pulls the first date on the last line into its own field?

By "last line," are you saying there is new line character before the closing date? In other words, every entry is created on a separate line? If so, that should be easy.

 

[gojets1721]

So in the resolution field of each complaint entry, there's multiple lines of follow-up that the user did with the customer, and it's formatted like this:

10/29/2021: Left message with customer to discuss further.

10/30/2021: Left second message.

11/1/2021: Patient called me back and I comped their stay. Complaint closed.

These notes are all in the resolution field, as its just a long text field. In the above example, its that 11/1/2021 date that I'm looking to pull into its own field because this is the date that the complaint was officially closed out

 

[theDBguy]

So in the resolution field of each complaint entry, there's multiple lines of follow-up that the user did with the customer, and it's formatted like this:

10/29/2021: Left message with customer to discuss further.

10/30/2021: Left second message.

11/1/2021: Patient called me back and I comped their stay. Complaint closed.

These notes are all in the resolution field, as its just a long text field. In the above example, its that 11/1/2021 date that I'm looking to pull into its own field because this is the date that the complaint was officially closed out

Try something like:

?CDate(Left(Mid([Resolution],InStrRev([Resolution],vbCrLf)),InStr(Mid([Resolution],InStrRev([Resolution],vbCrLf)),":")-1))

 

[gojets1721]

Try something like:

?CDate(Left(Mid([Resolution],InStrRev([Resolution],vbCrLf)),InStr(Mid([Resolution],InStrRev([Resolution],vbCrLf)),":")-1))

In an update query? Or in the code that you posted earlier?

 

[theDBguy]

In an update query? Or in the code that you posted earlier?

Don't do UPDATE query right away. Try using a SELECT query first and use that in a Calculated Column. You can forget the code I posted earlier, for now.

 

[gojets1721]

Okay I tried your above string in a select query but am getting a "#Func!" error in all the cells. Not sure why...

 

[theDBguy]

Okay I tried your above string in a select query but am getting a "#Func!" error in all the cells. Not sure why...

I might have had a typo. Could you try this one please? Thanks.

?CDate(Left(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),InStr(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),":")-1))

 

[gojets1721]

I might have had a typo. Could you try this one please? Thanks.

?CDate(Left(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),InStr(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),":")-1))

So I used the below code and access is prompting me with a input mask for 'vbCrLf'. I'm not sure what that means (i apologize for not being much help with these errors)

update tblcomplaints
set DateClosed = (Left(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),InStr(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),":")-1))

 

[theDBguy]

So I used the below code and access is prompting me with a input mask for 'vbCrLf'. I'm not sure what that means (i apologize for not being much help with these errors)

update tblcomplaints
set DateClosed = (Left(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),InStr(Mid(Resolution,InStrRev(Resolution,vbCrLf)+2),":")-1))

Replace them with the following:

Chr(13) & Chr(10)

 

[gojets1721]

Replace them with the following:

Chr(13) & Chr(10)

Replace what?

 

[theDBguy]

Replace what?

vbCrLf

 

[gojets1721]

Okay so it somewhat worked...but it's kind of weird. It's leaving out the first digit of the month

Meaning if the date was "9/27/2021", it pulled in "/27/2021" or if it was "10/12/2021", it pulled in "0/12/2021"

 

[theDBguy]

Okay so it somewhat worked...but it's kind of weird. It's leaving out the first digit of the month

Meaning if the date was "9/27/2021", it pulled in "/27/2021" or if it was "10/12/2021", it pulled in "0/12/2021"

That is interesting. I guess we'll have to adjust it a little. Try replacing the +2 with +1.