Unanswered: Invalid Results From Query - detailed explanation and example included
I have an issue that is proving to be extremely challenging. The problem is that I am getting invalid query results when running a query. Here is a brief narrative of the attached sample program:
There are 68,881 different records in the ‘Procedures’ folder. This folder contains the procedure code, procedure name, and two checkboxes representing ICD-9 or ICD-10 procedures (the only field of importance is the ‘procedure code’). Another folder is named ‘Completed Activities’. This folder contains the name of the person and a string of the ‘procedure codes’ that were performed (if multiple procedures were performed these are separated by a space character. Using the ‘For Each Match Using Regular Expressions’, a query is run (aptly named ‘Procedures Query’) to determine a match for these alphanumeric codes to actual ‘procedure codes’ when the ‘find answers’ button is pushed.
Looking at ‘daryl in the example, the string text in the ‘activities’ field pulls up the two proper ‘procedure codes’ and ‘procedure names’. However, when looking at the next person, ‘lynn’, the ‘activities’ of ‘4523’ pulls up both ‘4523’ and ‘04523ZZ’. The parameters used in the query were ‘=’ not ‘contains’ so the second activity is invalid. Looking at the third example, ‘bill’, the string of ‘009’ in the ‘activities’ field pulls up a plethora of wrong answers along with a single right answer.
I have struggled over this and tried doing creative things such as counting the field length of each activity encountered and comparing this against the field length of the ‘procedure code’, while this reduces some of the erroneous data that passes the query but not all. I have also added scripting that only allows one match on the query but it, too, allows some invalid values to pass the query. What can I do so that the query actually captures the right, single ‘procedure code’?
If nothing else, this example may serve as a basic primer on the usage of 'For Each Match By Regular Expression' because that can be very powerful (be careful though!!). I hope that someone can help me determine why and correct the issue with invalid query results.
I did quite an amount of testing with this problem and noticed, too, that the issue most likely appears when 'strictly' a number is entered. Given the constraints of the query '=' command, it should not pick up other values. I am smiling from ear-to-ear with MacHeus's solution because it appears to work in all cases. Now, I can safely move on to the next phase of this project!
And MacHeus, teşekkür ederim (I hope the translator worked okay on this).
Just as matter of interest added a user input query to manually select procedure (ie Bill's 0009) and only ! record returned but multiples with script even when a message box before query suggests "$test" variable is "0009".
Is the regular expression adding something - I don't know! Not familar with regexp matching - what is purpose of regexp match in your script?
RegExp allow you to search for certain characters, combinations of characters, and patterns. In my case, I wanted to list all of the procedures in a single field and separate each of these with a space (" ") character. When the 'For Each Match By Regular Expression' encounters a space it signifies that a new 'procedure' exists. My whole purpose was that in the original data the procedures were listed in 12 individual columns and that took up lots of space in the program. I took the original data, wrote some Visual Basic for Applications code in EXCEL that essentially joined the data in the 12 columns to a single string with a space in between. This greatly reduced the size of my database while allowing full access to the 'procedurs' for analysis.