Mathematical comparison operator in query

[kazaccess]

I have a form that takes in combo box values and then runs a query based on those values. One combo box contains the mathematical comparison operators (<,>,=,etc.) and the other is a text box formatted to general number. I am currently unable to string these together in my WHERE condition and get a correct result. The code and variations of it that I have tried in my WHERE condition are : [Score] & [Forms]![MyFormName]![MathematicalOperator] & [Forms]![MyFormName]![TextBoxNumber]. I am using this to query my table to find values >,<,=,>=,<=,<> to the number in the text box. For example I would like to get values where Score > 3 Any ideas on how to string this together properly?

[rzw0wr]

debug print this in the intermediate screen to see what it looks like.
Ctl+G for screen.


Dale

[kazaccess]

Sorry, I didn't specify. The query is able to execute, but it seems to be disregarding the condition.

[rzw0wr]

What shows in the intermediate screen?

Dale

[kazaccess]

I have my condition stored in a query and then a DoCmd.OpenQuery once I click a button. So it just opens up the query immediately. I apologize for not specifying this as well.

[rzw0wr]

I take it you are not going to answer my question.

Dale

[pbaldy]

Dale, I don't think VBA is being used here, so I don't think the question is being avoided so much as not understood.

kazaccess, I don't think this type of thing will work directly in a query. You'd probably have to build the query in code, which I suspect is what Dale thinks you're doing. There's a chance the Eval() function would work in the query, but I haven't tested it.

[kazaccess]

Ok thank you. I will try that and see how it goes.

[rzw0wr]

Thanks Paul. I thought they were making a WHERE in a form.

My Bad.

Dale

[kazaccess]

Ok so I've tried eval() and a CurrentDb.OpenRecordset and it is still not producing the correct result. I'm wondering if I'm maybe missing some sort of quotation syntax or something similar in my WHERE clause?

[pbaldy]

I did a quick test with Eval() and couldn't get it to work either. I suspect you'll have to do this in code, building an SQL string and including the operator from the form. Knowing the end goal would help. If you're using the query for a form or report, you can adapt this:

BaldyWeb wherecondition

to get the operator from the form as well as the value.

[kazaccess]

I've tried to adapt the wherecondition, but I do not plan on using the query in another form or report, so I don't think I'll be able to get it to work that way. Also, this is an Ad Hoc type of query designed for users who presumably do not have Access experience, so knowing the end goal won't be effective unless they know how to go into the SQL and change the operator themselves. I appreciate all the help. You would think that this type of thing would have a simpler solution, but I guess it's just an uncommon technique.

[pbaldy]

You can also change the SQL of a query with a DAO QueryDef. Bottom line is if you want to provide an interface like that, you'll need to build the SQL in code.

[ssanfu]

Not sure if this will help, but I found this mdb that has a form to create an ad hoc query. You will have to import the form into your dB - it requires a reference to DAO. Maybe you could add the operators to the form and code.
You can look at the code to generate the SQL and update a saved query instead of a list box.

THE ACCESS WEB
Adding a Search form to a database
http://access.mvps.org/access/forms/frm0045.htm