sqlms-accessms-access-2007query-designer

Pass combobox value as a comparison operator in access query?


I have a form with a combobox "cmb1" and a textbox "txt1". the combobox can be empty or can hold a comparison operator ">" or "<" or "=" without the quotes while the value of the comparison can be entered in the textbox txt1 as shown in the picture below frm1 and I have this query as shown below qry_employee

I tried to pass the value from the combobox as a comparison operator and the textbox value in the criteria in the "emplyee_age" field but I couldn't. for example

  1. If cmb1 is empty, then the query should show all employees.
  2. If cmb1 value is ">" and txt1 value is "22", then the query should show all employees whose age is greater then 22
  3. if cmb1 value is "=" and txt1 value is "30", then query should show all employees whose age is equal to 30 ... etc In short, the comparison operator should be taken from the value of the cmb1 while the value of the comparison should be taken from txt1 and both are used in the criteria field in the query. I no comparison operator is chosen, the query returns all values. I'm using MS Access 2007.

I tried for hours but couldn't get it to work. All your help is highly appreciated.


Solution

  • after so many trial and error, I have finally got it to work. It works perfectly. I will post it here for anyone who might search for the same thing :) here is the sql for the query

    SELECT tbl_employees.ID, tbl_employees.emplyee_name, tbl_employees.employee_age FROM tbl_employees WHERE (((IIf(IsNull([Forms]![frm1]![cmb1]),True,Eval(([tbl_employees].[employee_age]) & [Forms]![frm1]![cmb1] & [Forms]![frm1]![txt1])))<>False));
    

    Have a great day :)