Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    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.
    Attached Files Attached Files
    Daryl G

  2. #2
    Join Date
    Jul 2012
    Posts
    94
    Provided Answers: 4
    Something wrong with BD, i can not understand. But changing query window like this fix problem. See attached screenshot.
    Attached Thumbnails Attached Thumbnails qwindow.png  
    I do not know English well. I am using Google Translate for translation. Please do not laugh my sentences.

  3. #3
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    MacHeus,
    Your answer was INCREDIBLE!!! It worked... I consider myself very imaginative but I would not have known to try that. Thank you so much for your help.
    Daryl G

  4. #4
    Join Date
    Apr 2013
    Posts
    226
    Looks like a bug with the = operator when the value being compare to is a string made up of only numbers. Needs more testing to be sure.

  5. #5
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    matty,
    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).
    Daryl G

  6. #6
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi Daryl
    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?
    Regards
    David

  7. #7
    Join Date
    Apr 2013
    Posts
    226
    Hi David

    When I tested the query itself with user input I was getting multiple records returned. I also tried it with new variables not affected by the regexp and again multiple records returned.

    Matty

  8. #8
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    David,
    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.

    Regular expression - Wikipedia, the free encyclopedia
    Daryl G

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •