Iif statement and secondary evaluation criteria

R

Ray

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
 
M

Marshall Barton

Ray said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top