Problem using BuildCriteria() with reserved words...


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...


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

BTW: you can simplify
"'" & "IN*" & "'"
jg0069_2002Author Commented:
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.


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:
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

jg0069_2002Author Commented:
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.

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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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!
jg0069_2002Author Commented:
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.
Thanks, and good luck with your project!
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!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.