Problem using BuildCriteria() with reserved words...

Posted on 2006-04-12
Last Modified: 2008-01-16

Here's an interesting scenario that I ran into.  I've built a search interface for my Access database for users to use.  Everything works great.  To build my filter string, I use the Access BuildCriteria() function that has the following syntax:

strVar = BuildCriteria(fieldname, fieldtype, expression)

For example, I would have something like these examples

strVar = BuildCriteria("MyField",dbText,"SearchCriteria")
strVar = BuildCriteria("MyField",dbText, "Search1* or Search2*")

Which yields the following for strVar:

MyField like "SearchCriteria"
MyField like "Search1*" or MyField like "Search2*"

Now, that's all fine and dandy.  The only problem that I run into is when I use reserved words such as "IN" and "SELECT" as filter criteria.

For example, if I use

strVar = BuildCriteria("MyField",dbText,"IN*")

I get an error.  For an explanation, check out the following:

Microsoft says that if you want to use these reserved words, you have to put the apostrophies around them - BuildCriteria("FieldName", dbText, "'" & "IN" & "'")

That's all fine and well, but what if my criteria contains a boolean expression, like AND or OR and also contains a reserved word.  For example:

BuildCriteria("FieldName",dbText, "IN* OR SearchCriteria1")

Now, this will crash unless I use the apostrophies as such:

BuildCriteria("FieldName",dbText, "'" & "IN* OR SearchCriteria1" & "'")

Now, this yields the following:

FieldName like 'IN* OR SearchCriteria1"

However, I want it yield:

FieldName like 'In*' or FieldName like SearchCriteria1

Does that make any sense?  Anyways, not sure if anyone has run across this before.  But any information that you can provide would be greatly appreciated...


Question by:jg0069_2002
    LVL 39

    Expert Comment

    BuildCriteria("FieldName",dbText, "'" & "IN*" & "'") & " OR " & BuildCriteria("FieldName",dbText, SearchCriteria1)

    BTW: you can simplify
    "'" & "IN*" & "'"
    LVL 2

    Author Comment

    Thanks for the comment.

    I'll try that first thing in the morning.  The only problem that i see with it however is that I am allowing users to enter whatever criteria they want in a text box.  

    For example they could enter stuff like this:


    Basically, I'm giving the users the flexibility of entering whatever they want using boolean operators and wildcards.  I can then take BuildCriteria() of whatever they enter and it yields the filter string that I need.  The only problem I run into is when they use the reserved words IN and SELECT.

    I need to somehow look for "IN" or "SELECT" in the string and then do something with it to pass it into BuildCriteria(...).  If that makes any sense.


    LVL 39

    Expert Comment

    Yes, that makes sense.  What you would need to do is create a programming language parser since what you are doing is creating a mini programming language.  It would be fairly easy for just IN and SELECT but to handle all possible combinations....

    Perhaps looking at a different approach.  Did you consider using the built in filter by selection or filter by form capabilities?  Or maybe this:
    LVL 2

    Author Comment

    Thanks for the information.  I will look at the other approaches, but I'm sort of commited to the BuildCriteria(...) thing.  :(

    Creating a programming language parser.  Hmm, now that sounds interesting.  I've never created such a thing, so I really have no idea where to start on that, but I'm guessing I would look at the string using InStr(...) or maybe Replace(..) or something like that.  

    I really dont have any idea where to start, so maybe some pseudocode, code, or examples would be very very helpful.

    LVL 39

    Accepted Solution

    I have been involved with assembly language parsers, never natura1 language parsers.  But the basic steps are:
    1) Split a line into words
    2) One word at a time, compare that word against a table of reserved words
      2a) If a match is found, jump to some code to handle that word.
      2b) If a match is not found, compare the word against a table of variables
        2b1) If a variable match is found, replace the value
    3) Fetch the next word, goto 2
    The Intel family of processors hive some very powerful instructions for table lookup and jump which are not accessable from VBA.  (This is why VBA compilation is so fast.)  Here is some VBA pseudo code to handle the steps above:

    First create a reserved word table with two fields.
    IDnumber: Long, Incremental numbers but NOT an autonumber
    ReservedWord: String

    'create the variables we will need
    Dim i as integer, lngIDNumber As Long, strFunctionCall As String, ReturnValue
    'create a string array to store the words in a line
    Dim strWords()
    'split the input line into words
    strWords = Split(TheInputLine, " ")
    'look at each word one at a time
    For i = 0 To UBound(strWords)
       'look up the word in the table, if found return the IDNumber
       lngIDNumber = DLookup("IDNumber","tblReservedWords", "ReservedWord = " strWords(i))
       If IsNull(lngIDNumber) Then
           'if the word is not in the list do this
           'if the word is in the list, get the function name to call
           strFunctionCall = Choose(lngIDNumber, "FunctionName1", "FunctionName2", "FunctionName3", etc.)
           'call the function
           ReturnValue = Eval(strFunctionCall & "()")
       End If
    'Next word
    Next i

    It looks like this has expanded to a 500 point question -- yes?
    LVL 58

    Assisted Solution


    Sorry to come in late, but I use BuildCriteria() a lot, and thought I sould comment.

    Actually, I found that most users love it, provided you give them basic training, good error management and syntax feedback. In other words, the resulting string should be visible and should be tested.

    Basic code template, using txtCriteria, txtFeedback and txtError, try it on a new form to get the idea.

    Private Sub txtCriteria_AfterUpdate()

        Dim strCriteria As String

    On Error GoTo Bad_Criteria

        Me.txtFeedback = Null
        Me.txtError = Null
        If IsNull(Me.txtCriteria) Then Exit Sub
        strCriteria = BuildCriteria("MyField", dbText, Me.txtCriteria)
        Me.txtFeedback = strCriteria
        DLookup "MyField", "MyTable", strCriteria

        ' here we know that strCriteria is valid...
        ' the user can see what became of the entry.
        MsgBox "Syntax is correct!" & vbCrLf _
            & DCount("MyField", "MyTable", strCriteria) & " selected"

        Exit Sub
        Me.txtError = Err.Description
        If IsNull(Me.txtFeedback) Then Me.txtFeedback = "#syntax error#"
        Exit Sub
    End Sub

    You can provide a slightly more personal help by looking at the Err.Number, of course.

    Rewriting an expression analyser will not solve the problem. You would simply apply other rules and a new home-grown syntax, which the users would still have to learn, and you will certainly stop short of the full power provided by BuildCriteria. All loss and no gain.

    Consider these correct text criteria entries, and closely related errors and mistakes:

        *[0-9]*                       [0-9]*     [0-9]
        like "[A-D]*"                 Like [A-D]*     [A-D]*
        like N####                 N####
        "rock and roll"               rock and roll
        between 'in' and or        between in and or
        in(a,b,c)                     a,b,c
        'C++'                         C++
        "Power+"                    Power+
        'Fischer & Sons'            Fischer & Sons.
        '12 mod*'                    12 mod*
        '<*>'                         <*>
        > now()                      > now
        null                             = null    ""

    Basically, whenever an operator appears ('In' is an operator, but also +, &, mod, xor, and many others), you will have problems understanding what the user wants. If s/he knows how to add quotes when the expression is unclear, you will have no problems. But again, this requires both training and feedback.

    And a "help" button!

    I hope this gets you back on track ;)
    Good luck!
    LVL 2

    Author Comment

    Well, I've decided that it I didn't want to write a string parser which looks for instances of reserved words.  I think this kind of defeats the purpose of the BuilCriteria() Function.

    The database users are only searching using text, so the only problems they are having is with the reserved words "IN" and "SELECT".  What I've done is just took their search strings and searched for instances of " IN ", "IN ", " SELECT ", etc. and just replaced them by "*IN*" and "*SELECT*".

    It's kind of a crude approach, but it gets the job done.  And I found that since the application has been in service for about 6 months.  Only one person has searched for anything like this.  Yes, it's crude but it seems to be working.  And I couldn't justify spending all time to build a string parser.

    Thanks for the help guys.  I'll split the points.
    LVL 58

    Expert Comment

    Thanks, and good luck with your project!
    LVL 39

    Expert Comment

    You're welcome.  Glad to help and thank you very much for the points with "A" grade!

    Take a look at the link I gave you
    it will show you how to create a very powerful search utility your users can easily use.

    Happy Computing!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now