Can an update query be used to pull date from longtext field? (1 Viewer)

[gojets1721]

I've got an odd one. I have a DB where there's a long text field and for each record, this long text field always starts with a date.

I am trying to figure out a way to automate pulling this first date for each record and updating a separate field with it. I have thousands of records that are like this so automating this is critical.

Any suggestions on if that could be done with an update query? And if so, how exactly?

Very simple example DB attached. The description field's contents vary widely record to record, but every record always starts with a date.

 

[MajP]

It can be done in an update query, but I might do this in a recordset unless you can ensure me that it is always exactly in the format. Is it?

UPDATE tblComplaints SET tblComplaints.ComplaintStartDate = Trim(Left([complaintDescription],InStr([complaintDescription],":")-1));

 

[MajP]

So I tested with some garbage records and instead of choking completely it updated the valid ones.
tblComplaints tblComplaints

ComplaintNumber	ComplaintDescription	ComplaintStartDate
1​	1/2/2023: Investigation of complaint started.	1/2/2023​
2​	1/4/2023: Investigation has complaint begin.	1/4/2023​
3​	1/12/2023: Investigation of thiscomplaint has started.	1/12/2023​
0​	111/111/111: Not a date	​
0​	1/12/2023 is date no colon	​
0​	Nothing	​

You see in the one record without a colon you have a valid date and it choked. With a recordset update you could use regular expressions to find anything that looks like a date at the beginning. But if your data is clean as you show the simple update will work.

 

[theDBguy]

@gojets1721 In case you might find it useful, here's one example of how to use Regular Expressions. You just need to find the right pattern for what you're looking for in the data (i.e. the date).

 

[MajP]

@theDBguy
You forgot the link. But I think RegExp would give you a better chance of catching a date.

 

[theDBguy]

@theDBguy
You forgot the link. But I think RegExp would give you a better chance of catching a date.

How silly of me, sorry about that. Let's try it again.

Extract Data Using Regular Expressions

Earlier, I wrote this article for extracting e-mail addresses from a memo field using regular expressions. What it was able to do is retur...

thedbguy.blogspot.com

 

[ebs17]

Slightly adjusted and worded:

' in a standard module
Public Function ExtractDateString(ByVal AnyText As String) As String
    Static oRegEx As Object
    Dim oMatchCollection As Object
    
    If oRegEx Is Nothing Then Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "^\d{1,2}/\d{1,2}/\d{4}"
        Set oMatchCollection = .Execute(AnyText)
        If oMatchCollection.Count > 0 Then ExtractDateString = oMatchCollection(0)
    End With

End Function

UPDATE
   tblComplaints
SET
   ComplaintStartDate = CDate(ExtractDateString(ComplaintDescription))

ExtractDateString isolates the string corresponding to the date format. A part of a character string remains a character string at this point, this character string does not have to be a valid date.
CDate converts the character string into a date. Accepted character strings depend on your own regional settings.

 

[gojets1721]

It can be done in an update query, but I might do this in a recordset unless you can ensure me that it is always exactly in the format. Is it?

UPDATE tblComplaints SET tblComplaints.ComplaintStartDate = Trim(Left([complaintDescription],InStr([complaintDescription],":")-1));

This worked perfectly! Thank you!!