Problem Filtering table in a sub-form

[Xarkath]

Hello,

I am trying to filter a table viewed on a sub-form through criteria applied on the main form. There are 6 possible criterias and 5 of the 6 filters are set so that if the criteria is assigned then the form filters to that but if left blank then the filter is not assigned. Those five are working great but the 6th is slightly different and I cant figure it out.

The 6th criteria is based an a status. What I am trying to do is filter by the assigned status but if a status is not assigned then I want to see all records where the status is not "Complete".



I have tried multiple formulas but no matter what it continues to populate records with "Complete" as the status right along with all the others.


Here are just 3 of the formulas I have tried. I have also tried multiple variations using IsNull, Is Null and Eval.

[Forms]![Work Items]![Status] or <>”Complete”
<>"Complete" or [Forms]![Work Items]![Status]
IIF([Forms]![Work Items]![Status]="",<>"Complete",[Forms]![Work Items]![Status])


Any advice would be greatly appreciated.

Thanks

[June7]

Is the main form bound?

How are you applying the filter? Dynamic parameterized query? Or VBA code? I prefer the latter: http://www.allenbrowne.com/ser-62code.html

[ssanfu]

[Forms]![Work Items]![Status] or <>”Complete”

I think this won't work because you haven't provided a field to compare against.

This is similar to http://access.mvps.org/access/queries/qry0001.htm

Try

[Forms]![Work Items]![Status] or [Forms]![Work Items]![Status]<>”Complete”

[Xarkath]

Thanks for the replies. I will do my best to supply a little more detail.

The main form is unbound and consists of 6 combo boxes from which to select the filter criterias.

The subform is bound to a table (COBRA_Inv). To set the criteria on the sub-form I enter into the Record Source so that it looks like a Design Query. Then I enter the formulas within the Criteria row. The first of the 6 options is GrpNum so I have entered the formula as : [Forms]![Work Items]![Status] or [Forms]![Work Items]![Status] Is Null. This works as intended, If left null/blank then all records show otherwise only records where the group number matches GrpNum are displayed.

for the part I posted as <>"Complete" I have also tried applying it as [COBRA_Inv]![Status]<>"Complete" but like all the other variations it has had no affect.

Thanks

[ssanfu]

Since you can't get the filtering to work correctly, I would back June's suggestion about using VBA from Allen Browne's site to filter the records. Much better method to filter records.

[Xarkath]

Hello,

This is now solved but I am a little perplexed as to the problem. All I did was re-enter the formula but I left off the quotes on "Complete" and left them for Access to assign which it did. Comparing that to a screen shot I took the formula looks exactly like my last attempt but despite looks it now works.

Forms![Work Items]![Status] or [COBRA_Inv]![Status] <>"Complete"



Thanks

[June7]

The quote marks are different fonts in the original post. I have known Access to have issue with the slanted.