How to search certain column by using sql in advanced recordset?

[dbentley11]

I have some problem to search certain column based on customer's selection of "Search By" (e.g., name, keyword, or ID) when I am trying to write sql in advanced recordset. There  is an error message of wrong sql syntax when I used "IF/ELSE statement" before "SELECT FROM WHERE statement". Could I use IF/ELSE in advanced recordest or any solution to this problem? What's the correct syntax? Thank you for your input.    David

[bregent]

You can't use IF/ELSE in an advanced recordset unless it is supported by the DBMS and is within (not before) the select statement. Depending on the specific details, you might be better off using conditional statements in your scripting language to choose the appropriate SQL. We would need more details about what you are trying to accomplish before making specific recommendations.

[dbentley11]

Thanks a lot, Bregent:

We have a very big database, that's why I would like to search certain column based on user's selection of pulldown menu of "SearchBy" (5 possible choices). For example,  if they choose "Keyword", I can use 'Like' in WHERE statement; but if they choose "Cat. No.", I will use "=" in WHERE statement. Could I put IF/ELSE in WHERE Statement?

If not, what's an alternative solution if I can not use IF/ELSE for the condition statements?   David

[bregent]

For that, I would use the server side scripting language to construct the SQL statement dynamically.