Query records where data values meet a certain criteria within string

[KPR1977]

In an Access Database for field2, the data values could consist of the following:

---field1-------------field2-------
Widget 1-----112398764567
Widget 2-----987611236789
Widget 3-----112345678901
Widget 4-----543287652345

I need a query that will check every 4 characters in the string of field 2 and look for all instances of '1223' OR '9876' are found. So my results would be Widget 1, Widget 2, and Widget 3 records.

Normally, one could right a simple search criteria such as:
where field2 like '*1123*' or '9876'.

However in my case, I need it to check every 4 characters for a match to prevent results from pulling in strings like this: 051123874567

I hope this makes sense. Any ideas would be much appreciated! =)

[ADezii]

The SQL Gang will probably come up with a better answer, but this will work for now:

SELECT tblTest.Field1
FROM tblTest
WHERE ((Mid([Field2],1,4)="1123" Or Mid([Field2],1,4)="9876")) OR ((Mid([Field2],5,4)="1123" 
Or Mid([Field2],5,4)="9876")) OR ((Mid([Field2],9,4)="1123" Or Mid([Field2],9,4)="9876"));

[KPR1977]

Hi ADezii, thanks for the quick reply. The reason I'm wanting to avoid writing midstring functions is because the field I'm working with is over 1600 characters long. Maybe there is a VBA function that could accomplish this, but I'm too much of a novice to piece it together.

[ADezii]

Are you saying that Field2 can be up to 1,600 characters long and you wish to test each 4 character, consecutive block (1...4, 5..8, 1000...1004, etc.) against some value(s)?

[KPR1977]

@ADezii
This is precisely what I'm saying. ;-)

[NeoPa]

Well, ADezii's your man then. This is a situation where creating a public function which is called from within SQL is perfect for the job.

I can see no alternative to looping through the string checking each section for matches. Each section check could use InStr() though, as long as it's formatted with separators.

EG. "1223,9876". That would ensure no overlapping (Unnecessary in this case, but in case your question has been simplified I suggest it anyway).

[ADezii]

I'm assuming that it is more complicated than that, since Criteria Strings can be found in multiple locations within the same String in consecutive 4-Block Segments. For instance '1223' can be found at Position 1...5 (Block 1), 1001...1004... (Block 250), 1237...1240 (Block 309), while 9876 can also be found at 1560...1563 (Block 390). There can be many, multiple matches in a single String, which in my mind will not allow the use of a Public Function in an SQL Statement, but I could be complicating things as usual. I'll work on the most complicated scenario, then work backtrack if necessary..

[ADezii]

1. The following code will check every consecutive, 4-character block, in [Field2] for a possible match against either '1123' or '9876'. Should a match be found, the results will be written to a Table (tblResults) with the following data:
   1. Record Number where match was found.
   2. Character Position in [Field2] where match was found.
   3. The match string which was found.
   4. The value in [Field1] where the match was found.
2. Code:
   

   Dim intLengthOfString As Integer
   Dim intCharCounter As Integer
   Dim intBlockNum As Integer
   Dim strBuild As String
   Dim MyDB As DAO.Database
   Dim rst_1 As DAO.Recordset
   Dim rst_2 As DAO.Recordset
   Dim intRecNum As Integer
    
   CurrentDb.Execute "DELETE * FROM tblResults;", dbFailOnError
    
   Set MyDB = CurrentDb
   Set rst_1 = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
   Set rst_2 = MyDB.OpenRecordset("tblresults", dbOpenDynaset)
    
   With rst_1
     Do While Not .EOF
      intRecNum = intRecNum + 1
       For intCharCounter = 1 To Len(![Field2])
         If intCharCounter = 1 Then
           strBuild = Mid$(![Field2], intCharCounter, 4)
             If strBuild = "1123" Or strBuild = "9876" Then
               rst_2.AddNew
                 rst_2![Record#] = Format(intRecNum, "0000")
                 rst_2![Position] = Format(1, "0000")
                 rst_2![Match] = Mid$(![Field2], intCharCounter, 4)
                 rst_2![Field] = ![Field1]
               rst_2.Update
             End If
               strBuild = ""
         ElseIf intCharCounter Mod 4 = 0 And intCharCounter <> Len(![Field2]) Then
           strBuild = Mid$(![Field2], intCharCounter + 1, 4)
             If strBuild = "1123" Or strBuild = "9876" Then
               rst_2.AddNew
                 rst_2![Record#] = Format(intRecNum, "0000")
                 rst_2![Position] = Format(intCharCounter + 1, "0000")
                 rst_2![Match] = Mid$(![Field2], intCharCounter + 1, 4)
                 rst_2![Field] = ![Field1]
               rst_2.Update
             End If
               strBuild = ""
         Else
           'do nothing
         End If
       Next
         .MoveNext
     Loop
   End With
    
   rst_2.Close
   rst_1.Close
   Set rst_1 = Nothing
   Set rst_2 = Nothing
    
   DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
   DoCmd.Maximize

3. tblTest with Test Data:
   

   Field1      Field2
   Widget 1    1123444488889876120998976
   Widget 2    98761123678911237678
   Widget 3    1123456789019876987634545
   Widget 4    543287652345
   Widget 5    112345678907123409875654
   Widget 6    77776987655431123876544123
   Widget 7    111111111123
   Widget 8    6555555550984567
   Widget 9    98769276523417865309087654231234567896721123
   Widget 10   1123

4. Contents of tblResults after executing code:
   

   Record#    Field       Position    Match
   0001       Widget 1    0001        1123
   0001       Widget 1    0013        9876
   0002       Widget 2    0001        9876
   0002       Widget 2    0005        1123
   0002       Widget 2    0013        1123
   0003       Widget 3    0001        1123
   0003       Widget 3    0013        9876
   0003       Widget 3    0017        9876
   0005       Widget 5    0001        1123
   0007       Widget 7    0009        1123
   0009       Widget 9    0001        9876
   0009       Widget 9    0041        1123
   0010       Widget 10   0001        1123

5. This is all very preliminary, and probably not the optimal solution. I just want to make sure the concept is dead on! I'm sure that my colleagues will come up with some better, and more intuitive ideas on how to achieve the same results.
6. DOWNLOAD the Test Database that I used for this Thread to get a much clearer picture of what is going on.

[KPR1977]

Hey ADezii, first of all I test your code and it worked beautifully with a couple of different scenarios in my database. Major KUDOS to you and a big THANKS!!!

I do run into a snag when I attempt to execute this code on a table that holds over 80,000 records. It works fine when it only contains a few records, but this many records results in an "Overflow" error.

Not exactly sure why, but it might be something to do with the field being set to "Memo" instead of "Text", since Text only can hold 255 characters.

I wish I could attach the database so you could see what I mean, but even after compacting and closing it's over 600 megs. So except for the snag with this table (which very few users would probably ever encounter), the code is great!!!

Thanks again!!! :-)

[ADezii]

I'm fairly sure that I know where the Overflow Error is happening. I'm going to modify the code, then send it back to you. Hopefully, the Error should be eliminated. Done, Download the Attachment and see if the Error is eliminated.

P.S. - Just for my own curiosity, how long does it take to process the 80,000 Records?

[KPR1977]

Great, I'll give it a shot. I have to go offline for now, but I'm hoping to test it tonight or sometime tomorrow. I will definitely let you know the outcome! ;-)

[KPR1977]

@ADezii
Well the verdict is in...ADezii is the VBA Master!!! Wow, your code totally ran with no hangups! To answer your question, it took a little less than 20 minutes to process that many records, but my connection is a little slow.

Anyway, you nailed it like always! Thanks again so much!!!

I have a separate issue with the same database I'm working on. Here's a link to the thread. VBA to change maximum number of locks allowed on a recordset. If anyone is able to figure it out, I know it would be you. Thanks again for everything!!!

[NeoPa]

@ADezii
I think so I'm afraid. What I was suggesting was to work in the reverse direction from the usual way. It's unfortunate that the data worked with (from each record) is not in a more convenient format. If it had separator characters we could serch it using InStr(). It doesn't, so instead we create a string, with separators, which we check with each substring of the data in a loop. If a match is found anywhere in the process the function exits immediately returning True. If not then it processes to the end returning False.

This method is more flexible and can be used with very little change if the number of items to check against increases beyond the simple two that we start with here.

All that said, a working piece of code is a working piece of code, so another good solution from ADezii :)

I should add congratulations to KPR for expressing the situation so clearly in the first place. It is no accident that a solution was arrived at so straightforwardly in this case. A properly expressed question is so much easier to deal with.