Hello Everyone,

I'm suffering trying to get a Query Criteria working. My job is to
offer two unbound controls on a form "ValueAmountLK",
"ValueOperatortLK" and filter down the results, against a stored
field, PaymentAmount

ValueAmountLK unbound is Currency Field.
ValueOperatortLK unbound is a character Field, with values restricted
to ">","=","<"
PaymentAmount is a Currency Field being queried.

The bit challenging me is the query criteria....... (On the
PaymentAmount Field) - WHERE statement

Like IIf(IsNull([Forms]![TransactionBrowseForm]![ValueAmountLK]),"*",>
[Forms]![TransactionBrowseForm]![ValueAmountLK])

This works, if the field is empty - and I can also adjust the ">" with
"<" or "=" manually to give the results. But I am unable to
substitute the form control that the user can use the specify their
preference.

The outcome is the operator can select the two fields to show/filter
records to output for example >$1000 or = $32.45 or <$0.10c.


Thanks for listening. Any hints or tips would be appreciated!

Since the operator (">", "<", etc) cannot be a parameter, the best solution
will be to build a filter (or entire SQL statement) dynamically.

Where is the query headed? If the idea is to filter a form or report, you
could use something like this:
Dim strWhere As String
If Not (IsNull(Me.ValueAmountLK) OR IsNull(Me.ValueOperatortLK)) Then
strWhere = "[PaymentAmount] " & Me.ValueAmountLK & " " & _
Me.ValueOperatortLK
End If

You can then apply the filter to the form:
Me.Filter = strWhere
Me.FilterOn = True
or when you open the report:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

If you must do it in a query:
Const strcStub = "SELECT * FROM Table1 WHERE "
Const strcTail = " ORDER BY SomeField;"
CurrentDb.QueryDefs("SomeQuery").SQL = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<elias.farah (AT) scw (DOT) com.au> wrote