User to Define Query Parameters Through a Form

[samjh]

Hi,

Hope this is the correct part of the forum to use.

I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.

The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.

I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.

The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but evertime I try it I get a box appearing saying Property not Found.

Am I doing something wrong?

Thanks

 

[CJ_London]

Unfortunately the only way you can do this is to build the query in VBA. The only character you can use in this situation (with a proviso) is *

If you think about what your SQL looks like in a query it will be:

WHERE SomeField=[Forms]![myForm]![myControl]

which translates to

WHERE SomeField="<1234"

it converts to a string because < is not a numeric character so has to be a string

The * works with a proviso because you don't use = you use Like
WHERE SomeField Like [Forms]![myForm]![myControl]
WHERE SomeField Like "*abcd"

To build it in VBA you would have something like:

Dim SqlStr as String
SqlStr="SELECT * FROM myTable WHERE Somefield" & me.myControl

in this situation, the user would have to input the '=' sign otherwise the query would fail

Once you have built your sqlstr you could assign it to a querydef (which you can the export) or if you have a subform to display the data, you could assign it to that subforms recordsource.

The only other way would be to have some fairly complex conversions in your query and the <> in a separate control on your form so you would have something like

SELLECT * FROM myTable WHERE (iif([Forms]![myForm]![mysign]=">",myFld<[Forms]![myForm]![myControl],iif(([Forms]![myForm]![mysign]="<",myFld<[Forms]![myForm]![myControl],false))=true