By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Use operators in textbox for query

P: 4
Hi guys,

I have a form with an unbound textbox. It provides the parameter for a number field in a query.
I'd like for the user to be able to enter their own operators (">2" for example) and this gets passed to the query and ran.

The query works perfectly when the number is by itself, however when any operators are entered it errors (too complex or incorrect punctuation). I have tried all the combinations of " ' & etc. i can think of to pass it to the query criteria but it errors without fail.

Any help would be greatly appreciated as I've wasted hours and hours on this through trial and error (error, error) and googling.
It's driving me insane!

Many thanks,

Dan
Sep 20 '08 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You can't do this by passing a value as a parameter to a query - the database engine has no facilities for interpreting user-supplied parameters in the way you describe, as an operator followed by a value. It can be done by building an SQL string in VBA code, but my advice to you would be not to go this route at all.

Allowing free-form entry which then has to be interpreted can be fraught with error. What would you expect to happen if the user enters something like "> 2 < 5" for instance? This could not be passed to any query even in code without being expanded into the form

>2 AND <5

In my opinion what you want to do is simply not worth it. If you need to provide choices for users over the type of comparison, you could do it by constraining the choices to predefined operators using radio buttons and then build the SQL string yourself - but in my view this is an incredibly complicated approach to take. I would honestly suggest that the return for your efforts will not match the time and cost to achieve it.

As a project to teach myself about elements of compiler design I have in the past written an expression evaluator (in the programming language Pascal) which could evaluate any arithmetic expression entered, including the use of symbolic variables in an expression - say something like

x^2 + y + z - x * y

I remember spending more than three months working on this project. I learnt a great deal about compiler design, and interpreting expressions by doing this - including how an apprently simple expression is far from easy to interpret if you consider all the possible errors that may arise (missing operators, incorrect order, non-allowable characters entered, and so on).

If you expect to be able to pass a simple expression string entered by a user to a query and get it to make sense to the SQL engine more than occasionally I think you are heading down a very rocky path indeed.

-Stewart
Sep 20 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Hello, gentlemen.

Actually, VBA has Eval() function which could make it an easy trick.

Example:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.fld FROM tbl WHERE Eval(tbl.fld & Forms!frm!txbCriteria);
  2.  
Regards,
Fish
Sep 20 '08 #3

Expert Mod 2.5K+
P: 2,545
Thanks, Fish. Eval in the WHERE clause as you have shown is indeed a good way to go - at least so long as the user uses the right syntax for the comparison. As you say it makes it an easy thing to accomplish. I remain unconvinced of its worth, for the reasons I outlined, but it is at least possible using Eval to try it out and see whether or not your users are ok with it.

-Stewart
Sep 20 '08 #4

ADezii
Expert 5K+
P: 8,619
Thanks, Fish. Eval in the WHERE clause as you have shown is indeed a good way to go - at least so long as the user uses the right syntax for the comparison. As you say it makes it an easy thing to accomplish. I remain unconvinced of its worth, for the reasons I outlined, but it is at least possible using Eval to try it out and see whether or not your users are ok with it.

-Stewart
Hello Stewart & FishVal, if I may make a suggestion, the Expression to be Evaluated can be tested in the AfterUpdate() Event of the Text Box (txtCriteria) prior to execution, as in:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtCriteria_AfterUpdate()
  2. On Error GoTo Err_txtCriteria
  3. Dim strMsg As String
  4.  
  5. strMsg = "The Expression [" & Me![txtCriteria] & _
  6.          " ] cannot be Evaluated"
  7.  
  8. If IsNull(Me![txtCriteria]) Then Exit Sub
  9.  
  10. 'Will generate an Error if the Expression 
  11. 'cannot be evaluated
  12. Debug.Print Eval(Me![txtCriteria])
  13.  
  14. Exit_txtCriteria:
  15.   Exit Sub
  16.  
  17. Err_txtCriteria:
  18.   Me![txtCriteria] = "ERROR!"
  19.   MsgBox strMsg, vbExclamation, "Invalid Expression"
  20.   Resume Exit_txtCriteria
  21. End Sub
Expand|Select|Wrap|Line Numbers
  1. 'Will pass the Test and Evaluate to 30
  2. (((2 ^ 4) + 16 + 100 - 122) * 3)
  3.  
  4. 'Will generate an Error and display ERROR! in txtCriteria
  5. A * B / J
I was thinking basically to Trap any potential Evaluations prior to being used in an SQL Statement. What's your thought on this, guys?
Sep 21 '08 #5

Expert Mod 2.5K+
P: 2,545
Many thanks ADezii. Using error checking from the After Update event combined with Fish's suggestion of use of Eval for the Where clause is as good a solution as there is to this particular problem. Eval resolves the issue of interpreting user criteria, and the error checking copes with the potential for malformed expressions.

Thank you both for your well-informed suggestions.

Dan, if you follow Fish and ADezii's posts you will indeed have a viable way to interpret user expressions in a way which does not fall down if a malformed expression is entered. I think this is a good way forward - and I honestly didn't think there was one when I responded in post 2; I stand corrected there!

-Stewart
Sep 21 '08 #6

P: 4
Thankyou for the help Stuart, FishVal and Adezii.
I have a very simple and effective form set-up now to do the job.

Much appreciated,

Dan
Sep 21 '08 #7

ADezii
Expert 5K+
P: 8,619
One final point as far as Evaluating Expressions is the Order of Precedence. You must know the priority of Operators when being used within more complex Expressions, unless you rely exclusively on parenthesis. A couple of cases in point will demonstrate this point. In my previous example:
Expand|Select|Wrap|Line Numbers
  1. (((2 ^ 4) + 16 + 100 - 122) * 3) will Evaluate to 30
whereas
Expand|Select|Wrap|Line Numbers
  1. 2 ^ 4 + 16 + 100 - 122 * 3 will Evaluate to -234
and
Expand|Select|Wrap|Line Numbers
  1. (24 - 16) ^ 4 will Evaluate to 4096
whereas
Expand|Select|Wrap|Line Numbers
  1. 24 - 16 ^ 4  will Evaluate to -65512 
Sep 21 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.