Using operator signs with number in query

[Sianboy]

Hey All, I am a non-sql user here, who is linking criterias from forms to my query to generate reports and I facing a problem right now.

Currently, I have a combobox from a form that displays such values; "-","Less than","More than" and "Equal to" and there is a corresponding text box in the form whereby the user can key in the number. Eg, if I select "Less than" and I typed 3000 in the text box, the results displayed will be all data that is more than 3000.

To solve this, I tried using Like IIf([Forms]![Advanced Search Form]![Combobox]="-","*",IIF([Forms]![Advanced Search Form]![Combobox]= "Less than", < [Advanced Search Form]![Textbox])). But this cannot work as the access cant seems to recognise "<" and the number together. When I run the query with just < 3000 in the criteria box, then is alright no problem.

Is there any way to make this workable in the query itself? Thanks!

 

[Sianboy]

I see that helps a lot! My idea is what you said, linking what is selected in the combo box to the query for running. But in the first place, how to create a case statement for these?

>>>Hey All, I am a non-sql user here,<<<

As a non SQL user, I would suggest a non SQL approach, you could build a query for each condition you wanted to meet, and use a case statement to call each one of those queries depending on what is selected in the combo box.