Use operators in textbox for query

[DanHayden]

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

[Stewart Ross]

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

[FishVal]

Hello, gentlemen.

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

Example:

SELECT tbl.fld FROM tbl WHERE Eval(tbl.fld & Forms!frm!txbCriteria);
 

Regards,
Fish

[Stewart Ross]

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

[ADezii]

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:

Private Sub txtCriteria_AfterUpdate()
On Error GoTo Err_txtCriteria
Dim strMsg As String
 
strMsg = "The Expression [" & Me![txtCriteria] & _
         " ] cannot be Evaluated"
 
If IsNull(Me![txtCriteria]) Then Exit Sub
 
'Will generate an Error if the Expression 
'cannot be evaluated
Debug.Print Eval(Me![txtCriteria])
 
Exit_txtCriteria:
  Exit Sub
 
Err_txtCriteria:
  Me![txtCriteria] = "ERROR!"
  MsgBox strMsg, vbExclamation, "Invalid Expression"
  Resume Exit_txtCriteria
End Sub

'Will pass the Test and Evaluate to 30
(((2 ^ 4) + 16 + 100 - 122) * 3)
 
'Will generate an Error and display ERROR! in txtCriteria
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?

[Stewart Ross]

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

[DanHayden]

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

[ADezii]

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:

(((2 ^ 4) + 16 + 100 - 122) * 3) will Evaluate to 30

whereas

2 ^ 4 + 16 + 100 - 122 * 3 will Evaluate to -234

and

(24 - 16) ^ 4 will Evaluate to 4096

whereas

24 - 16 ^ 4  will Evaluate to -65512