By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Query records where data values meet a certain criteria within string

P: 23
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! =)
Mar 18 '10 #1

✓ answered by 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?

Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,638
The SQL Gang will probably come up with a better answer, but this will work for now:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.Field1
  2. FROM tblTest
  3. WHERE ((Mid([Field2],1,4)="1123" Or Mid([Field2],1,4)="9876")) OR ((Mid([Field2],5,4)="1123" 
  4. Or Mid([Field2],5,4)="9876")) OR ((Mid([Field2],9,4)="1123" Or Mid([Field2],9,4)="9876"));
Mar 18 '10 #2

P: 23
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.
Mar 18 '10 #3

ADezii
Expert 5K+
P: 8,638
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)?
Mar 19 '10 #4

P: 23
@ADezii
This is precisely what I'm saying. ;-)
Mar 19 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
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).
Mar 19 '10 #6

ADezii
Expert 5K+
P: 8,638
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..
Mar 19 '10 #7

ADezii
Expert 5K+
P: 8,638
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim intLengthOfString As Integer
    2. Dim intCharCounter As Integer
    3. Dim intBlockNum As Integer
    4. Dim strBuild As String
    5. Dim MyDB As DAO.Database
    6. Dim rst_1 As DAO.Recordset
    7. Dim rst_2 As DAO.Recordset
    8. Dim intRecNum As Integer
    9.  
    10. CurrentDb.Execute "DELETE * FROM tblResults;", dbFailOnError
    11.  
    12. Set MyDB = CurrentDb
    13. Set rst_1 = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
    14. Set rst_2 = MyDB.OpenRecordset("tblresults", dbOpenDynaset)
    15.  
    16. With rst_1
    17.   Do While Not .EOF
    18.    intRecNum = intRecNum + 1
    19.     For intCharCounter = 1 To Len(![Field2])
    20.       If intCharCounter = 1 Then
    21.         strBuild = Mid$(![Field2], intCharCounter, 4)
    22.           If strBuild = "1123" Or strBuild = "9876" Then
    23.             rst_2.AddNew
    24.               rst_2![Record#] = Format(intRecNum, "0000")
    25.               rst_2![Position] = Format(1, "0000")
    26.               rst_2![Match] = Mid$(![Field2], intCharCounter, 4)
    27.               rst_2![Field] = ![Field1]
    28.             rst_2.Update
    29.           End If
    30.             strBuild = ""
    31.       ElseIf intCharCounter Mod 4 = 0 And intCharCounter <> Len(![Field2]) Then
    32.         strBuild = Mid$(![Field2], intCharCounter + 1, 4)
    33.           If strBuild = "1123" Or strBuild = "9876" Then
    34.             rst_2.AddNew
    35.               rst_2![Record#] = Format(intRecNum, "0000")
    36.               rst_2![Position] = Format(intCharCounter + 1, "0000")
    37.               rst_2![Match] = Mid$(![Field2], intCharCounter + 1, 4)
    38.               rst_2![Field] = ![Field1]
    39.             rst_2.Update
    40.           End If
    41.             strBuild = ""
    42.       Else
    43.         'do nothing
    44.       End If
    45.     Next
    46.       .MoveNext
    47.   Loop
    48. End With
    49.  
    50. rst_2.Close
    51. rst_1.Close
    52. Set rst_1 = Nothing
    53. Set rst_2 = Nothing
    54.  
    55. DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
    56. DoCmd.Maximize
  3. tblTest with Test Data:
    Expand|Select|Wrap|Line Numbers
    1. Field1      Field2
    2. Widget 1    1123444488889876120998976
    3. Widget 2    98761123678911237678
    4. Widget 3    1123456789019876987634545
    5. Widget 4    543287652345
    6. Widget 5    112345678907123409875654
    7. Widget 6    77776987655431123876544123
    8. Widget 7    111111111123
    9. Widget 8    6555555550984567
    10. Widget 9    98769276523417865309087654231234567896721123
    11. Widget 10   1123
  4. Contents of tblResults after executing code:
    Expand|Select|Wrap|Line Numbers
    1. Record#    Field       Position    Match
    2. 0001       Widget 1    0001        1123
    3. 0001       Widget 1    0013        9876
    4. 0002       Widget 2    0001        9876
    5. 0002       Widget 2    0005        1123
    6. 0002       Widget 2    0013        1123
    7. 0003       Widget 3    0001        1123
    8. 0003       Widget 3    0013        9876
    9. 0003       Widget 3    0017        9876
    10. 0005       Widget 5    0001        1123
    11. 0007       Widget 7    0009        1123
    12. 0009       Widget 9    0001        9876
    13. 0009       Widget 9    0041        1123
    14. 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.
Attached Files
File Type: zip Test.zip (21.5 KB, 58 views)
Mar 19 '10 #8

P: 23
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!!! :-)
Mar 19 '10 #9

ADezii
Expert 5K+
P: 8,638
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?
Attached Files
File Type: zip Test_2.zip (18.4 KB, 62 views)
Mar 19 '10 #10

P: 23
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! ;-)
Mar 20 '10 #11

P: 23
@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!!!
Mar 20 '10 #12

NeoPa
Expert Mod 15k+
P: 31,494
@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.
Mar 20 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.