Solved Action Query Parameter Numerical Comparissons In VBA (4 Viewers)

[RonPaii]

Thanks, sorry there were some essentials I left out in the OP. I thought I'd be able to pass the operators as parameters; but you cannot do this. The SQL editor needs the operators or it'll error out when defining the query, so you cannot move forward to the next step.



I misread this thinking definintion in the VBA but Minty meant 'in the query SQL editor'. I thought in front of the parameter meant it was possible to put the operator in the query parameter; but he meant 'in front' of the queryParameter in the SQL Editor. Not 'in front' of the queryParameter in VBA as part of the query parameter definition.

I had the query already working for the above as a hardcoded queryDef passing var's for the operators & values & it is completely dynamic; but I was hoping to use a parameter query. But the drawback would be I would need to define each query separately; hardcoding the operators.
But I'm better off defining the SQL in VBA allowing the operators & parameters to be dynamic. It's a shame the query parameters dialogue does not have a datatype for operators. I thought the SQL editor would apply an implicit typecast with the operator ">" but it does not & I didn't for a moment think the SQL Editor parameter dialogue would not allow me to pass operators so didn't think I needed to raise the issue in the OP - again my apologies.



This had me perplexed as I thought you meant somewhere in the VBA SQL; not the SQL Editor I realize you were now referring to. But I did not make it clear I needed the operators to be dynamic.

Thanks again chaps.

No reason you can't build the query testing multiple comparisons by also testing a 2nd parameter for which one will be true.

Where ([FieldToTest] > [Param1] and [Param2]=">")
   or ([FieldToTest] < [Param1] and [Param2]="<")

 

[Minty]

If you are doing this dynamically on a continuous form (e.g. up/down arrows for example), you only ever need to move the item up or down one position, so you only need to adjust the previous or next item in the list and replace their sort order with the original.

It makes it much simpler.