String search expression with wildcards for calculated field

[Medwar19]

Hi all,

I have a table with Field1 containing strings. Within each string is an error code that I need to extract for use in Field2 in the same table.

i.e.

Field1

the err 000 111 222 333 444 occurred at 12:56:00 jan 05

Field2

000 111 222 333 444

There position of the code in the string is random and I need to pattern match the format as other numbers are contained in the strings too. The codes however always have the same format:

"### ### ### ### ###"

so my question is how to extract the codes from Field1.

The instr function doesn't take wildcards and the like function only tells you if the pattern exists not where it is.

I found John Nuricks rgxExtract() macro but the Expression entry in table Design View doesn't recognize it.

Please, is there a way of extracting the variable codes from strings?

Thanks for your time. Much appreciated.

Mark.

[ADezii]

1. First and foremost, some basic assumptions:
   1. Your Table name is tblStrings (can be any Name).
   2. tblStrinigs consists of a single Field named [Field1].
   3. [Field1] is defined as: {TEXT 255}, Required.
2. Copy-N-Paste the following Function to a Standard Code Module:
   

   Public Function fExtractECodes(strString As String)
   Dim varString As Variant
   Dim intCounter As Integer
   Dim strBuild As String
   Dim intNumOfMatches As Integer
    
   varString = Split(strString, " ")
    
   For intCounter = LBound(varString) To UBound(varString)
     If Len(varString(intCounter)) = 3 And IsNumeric(varString(intCounter)) Then
       intNumOfMatches = intNumOfMatches + 1
         strBuild = strBuild & " " & varString(intCounter)
     End If
   Next
    
   If intNumOfMatches = 5 Then
     fExtractECodes = Trim(strBuild)
   Else
     fExtractECodes = Null
   End If
   End Function

3. Create a Query with [Field2] as a Calculated Field. The Value in [Field1] will be passed to the Public Function, and an appropriate Value returned.
   

   Field2: fExtractECodes([Field1])

4. The Logic in fExtractECodes() will search for a specific Pattern of five, sequential, 3-character, space-delimited, digit blocks, namely:
   

   ### ### ### ### ###

   then return the correct results.
5. Sample Data (tblStrings):
   

   Field1
   the err 000 111 222 333 444 occurred at 12:56:00 jan 05
   Not even a digit, let alone sets of 3
   fgfgfg wdees sddsds 111 222 333 444 555
   dfshhg  fdmnbhj bj 444 555 777 888 jhfjhf
   111 333 444 44 666 1 digit short on the 4th set
   123 444 666 777 888 dasdsds fgghgf hhjjh jh jhkjh jhjhhj hjhj
   She sells sea shells at the sea shore
   Front 238 999 765 4 2 digits short on back end
   123 444 666 777 888 dasdsds fgghgf hhjjh jh jhkjh jhjhhj hjhj

6. Query Definition:
   

   SELECT tblStrings.Field1, fExtractECodes([Field1]) AS Field2
   FROM tblStrings;

7. Output after running Query:
   

   Field1                                                           Field2
   the err 000 111 222 333 444 occurred at 12:56:00 jan 05          000 111 222 333 444
   Not even a digit, let alone sets of 3    
   fgfgfg wdees sddsds 111 222 333 444 555                          111 222 333 444 555
   dfshhg  fdmnbhj bj 444 555 777 888 jhfjhf    
   111 333 444 44 666 1 digit short on the 4th set    
   123 444 666 777 888 dasdsds fgghgf hhjjh jh jhkjh jhjhhj hjhj    123 444 666 777 888
   She sells sea shells at the sea shore    
   Front 238 999 765 4 2 digits short on back end    
   123 444 666 777 888 dasdsds fgghgf hhjjh jh jhkjh jhjhhj hjhj    123 444 666 777 888

8. If you simply wish to return the Records in [Field1] that meet the criteria, without actually extracting the Values themselves, then:
   

   SELECT tblStrings.Field1
   FROM tblStrings
   WHERE tblStrings.Field1 Like "*### ### ### ### ###*";

[Medwar19]

Hi ADezii,

First of all THANKS!!!!

Exactly what I was trying to do. Excellent layout to your answer, I really learned a lot with this problem and really
appreciate your reply.

I got caught up trying to solve it with expression builder but with your example found writing directly in VBA and SQL is much more flexible.

All the best,

Mark

[ADezii]

Glad the solution suited your needs.