• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Problem using BuildCriteria() with reserved words...

Folks,

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:

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B197586

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

Thanks,

JRG
0
jg0069_2002
Asked:
jg0069_2002
  • 4
  • 3
  • 2
2 Solutions
 
thenelsonCommented:
Try:
BuildCriteria("FieldName",dbText, "'" & "IN*" & "'") & " OR " & BuildCriteria("FieldName",dbText, SearchCriteria1)

BTW: you can simplify
"'" & "IN*" & "'"
to
"'IN*'"
0
 
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:

(1)  IN PROGRESS OR (CRITERIA2 AND CRITERIA3)
(2)  (IN* AND OUT*) OR SELECT* OR SOMEOTHERCRITERIA

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.

Thanks,

JRG
0
 
thenelsonCommented:
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:
http://www.mvps.org/access/forms/frm0045.htm
http://www.mvps.org/access/modules/mdl0056.htm
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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.

0
 
thenelsonCommented:
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
   Else
       '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?
0
 
harfangCommented:
Hello,

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
   
Bad_Criteria:
    Me.txtError = Err.Description
    If IsNull(Me.txtFeedback) Then Me.txtFeedback = "#syntax error#"
    Err.Clear
    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!
(°v°)
0
 
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.
0
 
harfangCommented:
Thanks, and good luck with your project!
(°v°)
0
 
thenelsonCommented:
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
http://www.mvps.org/access/forms/frm0045.htm
it will show you how to create a very powerful search utility your users can easily use.

Happy Computing!

Nelson
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now