Iif statement and secondary evaluation criteria

[Guest]

I am trying to use a text box formatted as a number as a criteria in a query.

If the box is mt I 3want to select all the records, and if it is not mt I
want to use a <= criteria on the number.

I have got a statement that will work except for the 2nd part of the criteria.

IIf(IsNull([Forms]![Report Builder Form]![Text99]),Is Not
Null,([members].[Year Paid])<=[Forms]![Report Builder Form]![Text99])

The 2nd part ,([members].[Year Paid])<=[Forms]![Report Builder
Form]![Text99]) returns no records although if I use it separately as a
criteria by itself it returns the correct records.

I just dont know how to get the Less Than Equal To thing working in the Iif
statement.

Any help appreciated.

Ray

 

[Marshall Barton]

I am trying to use a text box formatted as a number as a criteria in a query.

If the box is mt I 3want to select all the records, and if it is not mt I
want to use a <= criteria on the number.

I have got a statement that will work except for the 2nd part of the criteria.

IIf(IsNull([Forms]![Report Builder Form]![Text99]),Is Not
Null,([members].[Year Paid])<=[Forms]![Report Builder Form]![Text99])

The 2nd part ,([members].[Year Paid])<=[Forms]![Report Builder
Form]![Text99]) returns no records although if I use it separately as a
criteria by itself it returns the correct records.

Try this criteria on the [Year Paid] field:

<=Forms![Report Builder Form]!Text99 OR
Forms![Report Builder Form]!Text99 Is Null