VBA code for two-item filter?

[WinDancer]

My code for an option group [below] works fine. I now need to to another option group that checks both CaseStatus and assignedto.

Some help with the if statement to require both matches?

Thanks,
Dave

Private Sub Frame22_AfterUpdate()
Frame33 = Null

If Frame22 = 1 Then
DoCmd.ApplyFilter , "[casestatus]='open'"
End If

If Frame22 = 2 Then
DoCmd.ApplyFilter , "[casestatus]='closed'"
End If

If Frame22 = 3 Then
DoCmd.ApplyFilter , "[Assignedto]= 'Unassigned'"
End If

If Frame22 = 4 Then
DoCmd.ApplyFilter , "[casestatus]<>null"
End If

End Sub

 

[boblarson]

First of all, get rid of all of the IF's on this one and use a select case instead and call the apply filter once.

Private Sub Frame22_AfterUpdate()
Dim strCriteria As String
Frame33 = Null

Select Case Me.Frame22
   Case 1
        strCriteria =  "[casestatus]='open'"
   Case 2
        strCriteria = "[casestatus]='closed'"
   Case 3
        strCriteria = "[Assignedto]= 'Unassigned'
   Case 4
        strCriteria = "[casestatus]<>null"
End Select
   DoCmd.ApplyFilter , strCriteria

End Sub

Not sure what other criteria you want. Can you be more explicit?

 

[pbaldy]

The filter would look like:

DoCmd.ApplyFilter , "[casestatus]='closed' AND [Assignedto]= 'Unassigned'"

 

[WinDancer]

What I am doing is selecting the case status by whatever user is logged in to the computer.
The 'and' works great but it doesn't like the environs....

 

[pbaldy]

Oops, I typed too slowly earlier. We would need to see how you're using the Environ() function.

 

[WinDancer]

I forgot to attach it- sorry

If Frame33 = 1 Then
DoCmd.ApplyFilter , "[casestatus]='closed' AND [Assignedto]= Environ(UserName)"

Thanks,
Dave

 

[pbaldy]

Try

DoCmd.ApplyFilter , "[casestatus]='closed' AND [Assignedto]= '" & Environ(UserName) & "'"

 

[WinDancer]

Doesn't like that, either

rTime error 5
Invalid procedure / call

 

[boblarson]

Gotta have quotes around UserName:

DoCmd.ApplyFilter , "[casestatus]='closed' AND [Assignedto]= '" & Environ("UserName") & "'"

 

[pbaldy]

Duh! I knew that. Got in at 5:30 to work on servers, obviously brain is starting to cramp. In the immortal words of Ten Years After, "I'm going home"!

 

[WinDancer]

That works! I can now figure out the different options my users need.
Thank you both for your help.
Dave

 

[boblarson]

In the immortal words of Ten Years After, "I'm going home"!

I wish I could go home now...

 

[WinDancer]

I am down to my last 10 paydays...
Finally !