Form Filter Property using Nested Iif Statement (1 Viewer)

[Ray Spackman]

Is it possible to use a nested Iif statement in the form filter property?

If so can someone show me some quick syntax on how this could be accomplished. I am using 3 different query field null values together with and/or in the 3 level nested iif statement.

If this is not possible, would building a function or sub routine be the best route?

As always, thanks in advance for help, time, and effort.

 

[jdraw]

Ray,
It would be easier to understand if you
- would tell us in plain English what you are trying to filter
ie -- what is the logic

and - show us what code you have so far to achieve this filter.

 

[Ray Spackman]

I apologize, I was trying to keep it short winded. Too short I guess.

I have a form based on a query where the query has several criteria for several fields.
On this form I am am using a text box that counts (sums using if statements) for the control source of this text box. Then when the form is open and I click on this text box, it filters the form (using Filter and Filter On) so the filtered records can be edited. I have all of the text boxes using only basic criteria working perfectly but have now com to a few that are more complicated.
Lets say that I have 3 text boxes on the form (that actually date formatted and are fields from the query). We will call them dteDate1, dteDate2, dteDate3 respectively.
I create an unbound text box to count(using sum and if statements) in its control field; looking something like this:
=Sum ( IIf ( IsNull (dteDate1) =True And IsNull (dteDate2)=True And IsNull (dteDate3)=True , 1, IIf ( IsNull (dteDate1)=True And IsNull (dteDate2)=True And IsNull (dteDate3)=False , 1, IIf ( IsNull (dteDate1)=True And IsNull (dteDate2)=False And IsNull (dteDate3)=True , 1, 0) ) ) )
I do not have an issue putting this in the control of the text box. But when clicking on this text box, I need to be able to filter the records using this criteria. So my question is, is there way to use this in the forms Filter Property to accomplish this (will this property take this type of Iif statement criteria (getting rid of the sum function obviously). And if so I am not sure how that syntax would look; as I know this property has a little different syntax than vba. If this is not possible, would a building a function or sub-routine be better to create this filter?

 

[Ray Spackman]

Re: SOLVED Form Filter Property using Nested Iif Statement

Okay I found out that I was totally overthinking this whole thing. After re-evaluating my goals, I found that I could accomplish the task without needing nested Iif Statements which allowed me to filter the form the way I needed.

Thank you to those who viewed and tried to help.