Filter VBA code

[reggiete]

Hello All,

I am needing help with adding an additional criteria to the below VBA language.

Me.MSProductionSubform.Form.Filter = "[Status] = 'MLO Evaluation Completed' And [Assigned Analyst] = '" & Me.UserQueueSelected & "' And [Testing Month] = '" & Me.Testing_Month & "'"
Me.MSProductionSubform.Form.FilterOn = True


Where the status = MLO Evaluation completed, i would like to also add in 'Final QC Completed

So to conclude, i would like the status = 'MLO Evaluation Completed' And 'Final QC Completed, Final QC Incomplete.

 

[MarkK]

I don't understand the rule you mean to apply here . . .

Where the status = MLO Evaluation completed, i would like to also add in 'Final QC Completed

So to conclude, i would like the status = 'MLO Evaluation Completed' And 'Final QC Completed, Final QC Incomplete.

The red and the blue seem like contradictory criteria, but you present them in the same expression.

 

[reggiete]

the below code only filters my form where the status is 'MLO Evaluation Completed'

Me.MSProductionSubform.Form.Filter = "[Status] = 'MLO Evaluation Completed' And [Assigned Analyst] = '" & Me.UserQueueSelected & "' And [Testing Month] = '" & Me.Testing_Month & "'"
Me.MSProductionSubform.Form.FilterOn = True

I would like to add additional criteria on the Status column. So i would like to not only filter by MLO Evaluation Completed, but add in Final QC Completed and Final QC Incomplete.

 

[Frothingslosh]

Yeah, he gets that. What Mark pointed out was that Final QC Completed and Final QC Incomplete seem to be completely contradictory, as in if one is true, then the other MUST be false, making one of the two utterly redundant.

 

[reggiete]

Oh ok, well in this case there are times when records will have the Status as Final QC Incomplete. See Below

John Doe Has Record 1. He completes his work and flagged as MLO Evaluation Completed.

Jane Doe see record 1 is completed based on the status. Jane doe does a review to make sure the record is complete. if lshe is unable to complete her final review. the record will move from "MLO Evaluation Completed to Final QC Incomplete.

If Jane Doe is able to complete her final review, the status will now be " Final QC Completed"

So record 1 can have those status even though it looks redundant.

 

[reggiete]

For John doe, i have a button on a form the user can press to view Completed record. I need the above code, to filter the status column to "MLO Evaluation Completed, Final QC Completed, Final QC Incomplete". this will allow the user to see all records he completed.

 

[MarkK]

Then maybe you need to use OR, not AND . . .

If (Status = "NotComplete" OR Status = "AlmostComplete" OR Status = "CompletelyComplete" ) AND . . .

Is that what you are trying to do?

 

[reggiete]

yes thats what im trying to do, but i need to know how do i add it to the code below

Me.MSProductionSubform.Form.Filter = "[Status] = 'MLO Evaluation Completed' And [Assigned Analyst] = '" & Me.UserQueueSelected & "' And [Testing Month] = '" & Me.Testing_Month & "'"
Me.MSProductionSubform.Form.FilterOn = True


I know i need to use And but i need to add the additonal criteria to filter on the Status column

 

[MarkK]

If you use AND the expression will never return any records. Like, this expression . . .

Status = "Complete" AND Status = "Incomplete"

. . . will never evaluate to True because the same status--unless we are talking about Schrödinger's status--can never be two different things at the same time. You must use OR.

https://en.wikipedia.org/wiki/Schrödinger's_cat