Good day to you all,

I wish to be helped out. I have a query which takes an input from a form (let's say it is called "Form1") and there is one field in that query which is called "TotalCost". I need to filter that query based on a Min and Max "TotalCost " in order to see the items which have Total Costs between the Min & Max value. Now the point is that the same query takes another input from "Form1" and sometimes i need to filter the query based on the "City" and sometimes based on the "TotalCost" and I need to have the freedom to filter based either one of them or both so that if i choose to filter based on certain "cities" I can see all "TotalCost" related to them and vice versa and in case i need to see certain "cities" with certain "TotalCost" still I can do so. If I use { Like "*" & } tool then it will not work with the "TotalCost" field because it will show all the records which contain that digits I put in "Form1" input! So how to go around this situation?
I hope i made my self as clear as possible and sorry for the lengthy message.
Appreciate your valuable feedback

---

Reply from Richard Rost:

This sounds like a pretty complex form/query combination. Your BEST bet is to probably build yourself a dynamic SQL statement to feed your form, like I show in my SQL Seminar. With many variables like this, a simple query might become too complex to work with easily.

For example, filtering by total cost is no problem. You just put a criteria in your date field that says:

BETWEEN Forms!MyForm!CostMin AND Forms!MyForm!CostMax

But now when you start adding cities and such in there, you're going to be getting into multi-row criteria, NULL situations, and such. Better to just build a query like this in SQL.