returning all records if form field is null (1 Viewer)

[nonrev1]

Using the below expression a user can retrieve revenue values typed in the blue_min and blue_max fields.

>=[Forms]![Switchboard Criteria Form]![blue_min] And <=[Forms]![Switchboard Criteria Form]![blue_max]

However, in this case the users wants all values equal to/greater than $100,000 or blue_min.

How does one write an expression to accomplish both. Allow the user the flexibility to limit blue_max or to not limit blue_max?

Can the expression be written if a value is not entered in the blue_max field it will get all records >=$100,000?

 

[Jon K]

(1) "... in this case the users wants all values equal to/greater than $100,000 or blue_min."

Revenue >= $100,000 Or Revenue = blue_min


(2) "... if a value is not entered in the blue_max field it will get all records >=$100,000?"

Revenue >= $100,000

(i.e. ignore blue_min)


A little clarification is needed. Which case above do you want when blue_max is left empty?

 

[nonrev1]

Hi John K,

Thanks for your reply. There are two fields blue_max and blue_min. It is currently working if both fields are given a value. If either field is left blank, we get error messages. We would like it to work if one of the fields is given a value and the other is left blank.

So if blue_min is given a value of 100,000 and blue max is left blank, the user would receive all values equal to or greater than 100,000.

If blue_min is left blank and blue_max is given a value of 500,000, the user would receive all values equal to or less than 500,000.

Sorry for the confusion. I guess my topic title really did not correspond with what I was trying to communicate. I hope this clarifies... TIA for any help.

 

[Pat Hartman]

Your selection criteria needs to be expanded. I'm going to show you how using dummy names so the answer is shorter and easier to understand.

(YourFld >= FormFldMin OR IsNull(FormFldMin)) AND (YourFld <= FormFldMax OR IsNull(FormFldMax))

The parentheses are CRITICAL since the condition combines AND and OR operations.

 

[nonrev1]

Pat,
Thank you for you assistance... I think we are on right track, however, we can't seem to make it work. We are confused by the "Yourfld" part of the expression?

(YourFld >= FormFldMin OR IsNull(FormFldMin)) AND (YourFld <= FormFldMax OR IsNull(FormFldMax))

 

[nonrev1]

THANKS!

We got it to work. I am sure the information that we gave was probably not complete enough but by your replies compared with some other expressions we did we came up with this:

(>=[Forms]![Switchboard Criteria Form]![blue_min] Or ([Forms]![Switchboard Criteria Form]![blue_min]) Is Null) And (<=[Forms]![Switchboard Criteria Form]![blue_max] Or ([Forms]![Switchboard Criteria Form]![blue_max]) Is Null)

Our project is almost complete. We couldn't have done it without the AWESOME help available via the Access World Forums! Special thanks to Pat Harman who solved several mysteries for us and thanks to everyone else who replied.