Passing > or < from a form

[Guest]

I have a form with the following controls

Combo30
Value List "> Greater Than";"< Less Than"

Text42
Unbound where I enter a number

Text44
Unbound but with the following data source
=Left([Combo30],1)+[Text42]

So if I select > Greater Than from the Combo Box and enter 2007 in Textbox
42 I get >2007 in Textbox 44.

So Far so good.

Now I have a query with a year field in it. I want the criteria of the
query to be passed from the form control Textbox44. I have tried different
ways but cannot get it to work.

If I enter >2007 directly in the query criteria it works, but if I enter
Forms![Report Builder Form]![Text44] in the criteria it does not.

Can anyone help me out.

Thank you.

Ray

 

[Allen Browne]

You are trying to pass the operator (equal, greater than, ...) as well as
the value. You cannot pass the operator to a query in a parameter like that.

Therefore you need to find an alternative approach. Where is this query
headed? If the idea is to show the results in a form, you could remove the
criteria from the query and set the form's Filter instead. If the target is
report, you could use the WhereCondition of OpenReport. If you must get it
into the query, you could create the entire SQL statement, and assign it to
the SQL property of the QueryDef.

For a simple example of using OpenReport with a WhereCondition, see:
http://allenbrowne.com/casu-15.html

Your WhereCondition might be:
strWhere = "[SomeField] " & Me.combo30 & " " & Me.Text44

 

[Michel Walsh]

That won't be very fats, in fact, it will be probably quite slow, but you
can try:


WHERE iif(FORMS!formName!Combo30=">" , FieldNameHere >
FORMS!formName!Text42 , FieldNameHere < FORMS!formName!Text42 )


Maybe a little bit faster, I haven't tried:


WHERE (( FieldNameHere > FORMS!formName!Text42 ) IMP
(FORMS!formName!Combo30=">" ) )
AND (( FieldNameHere < FORMS!formName!Text42 ) IMP
(FORMS!formName!Combo30="<" ) )



Finally, you can also try:


WHERE eval( FieldNameHere & "FORMS!FormNameHere!Combo30" &
FORMS!FormName!Text42 )





Hoping it may help,
Vanderghast, Access MVP