Query criteria from form using operators

[maGemme]

Hi everyone, I know that this has been discussed at large in the past and I have found great info in the past threads but none that answered my dilema.

I have a query, the criteria is pulled from a form's combo box.

Here's my code

Field : Treatment Time: DateDiff("d",[tblRequests]![Date],[tblRequests]![Date_comp])
Show : True
Criteria : [Forms]![frmRequests_Search]![cbo48] Or [Forms]![frmRequests_Search]![cbo48] Is Null

This works great if I put in solid values such as 1, 2, 3 etc. But what I need is to pull things less than 2, more than 3 etc. So I'm thinking just put in the drop box the values "<=2" and ">3" but it doesn't return anything.

When I put "<=2" or ">3" directly in the criteria it works fine.

I must be missing something, why isn't it recognizing what's in my drop box as a proper criteria?

 

[DCrake]

What you may be better off doing is to have an intermediate combo box that holds the operators as value lists

Me.CboOperator.Rowsource = "Less Than";"<";"Less than or equal to""<=","Equal To";"Between";"Between";Etc

Next have your field where you enter the criteria
Then when building your sql string

sSql = "[FieldName]" & Me.CboOperator & Me.Criteria

Lets say the user selects "Less Than" in the CboOperator and 3 in the criteria textbox, sSql would convert to

[FieldName]<3

You would have to be careful when constructing your sql to contend with text, number and date type controls.

 

[maGemme]

That's a pretty decent solution but I don't really want my users to specify what they want.

Let me explain in more details what I have.

Basically I have an entry form that records the date we received a request and the date on which the request was done and filed.

Then I have a form with a bunch of unbound combo + text boxes that act as a filter for a subform below. The subform shows filtered results from the table.

Now I'd like to have a check box that the user could use to filter accounts that took less than 48 hours to resolve. I'm thinking that using datediff and then finding the results less than or equal to 2 (days) would give me the results.

Now the solution I thought of was to put an If condition as a criteria but I have no idea the syntax to use.

As my query criteria I would have something like :

If Forms!FormX!checkboxY = 1 then (something refering to this present criteria) <=2 else >=0

but I know that's not right...

 

[boblarson]

As I found out recently, you can NOT use operators like this in a regular saved query. You need to use VBA to build the query itself if you want to be able to use a selection for the >, >=, <, <= operators.