Filter multiple criteria

D

demogorgan

Registered User.
Local time
Today, 22:42
Joined
Nov 29, 2017
Messages
37
Hi,

So I have a form where the user selects a customer from a combo box - this then applys a filter to only show the results for that customer chosen in the combo box.

This works fine but I would like it to only show outstanding orders for the customer also, this is indicated by a check box called Order_Complete.

I have tried to add in a second filter but I cannot for the life of me get it to work and it stops the first filter working. I am struggling to get my head around the use of the AND statement which I am assuming is what I am missing to get this to work.

Code: 
Private Sub cboTrustee_AfterUpdate()


If IsNull(Me.cboTrustee) Then
        Me.FilterOn = False
    Else
       Me.Filter = "Trustee = """ & Me.cboTrustee & """"
        Me.Filter = "Order_Complete = """ & Me.Order_Complete = 0 & """"
                
        Me.Filter = strFilter
        Me.FilterOn = True
End Sub

Thank you in advance
Demo
 
I think you will need to add the strings
I'm surprised it works at all as strfilter is not set?
I'd also check your string is formatted as you think it is, wouldn't you just want
Order_Complete =0 ?

HTH
Code: 
strFilter = "Trustee = """ & Me.cboTrustee & """"
strFilter = strFilter & " AND Order_Complete = """ & Me.Order_Complete = 0 & """"
 
Last edited:
something like this may work (untested)

Code: 
Private Sub cboTrustee_AfterUpdate()

Dim strFilter as string

If not IsNull(Me.cboTrustee) Then
strFilter = strFilter & "Trustee = """ & Me.cboTrustee & """ And "  ' if string
'strFilter = strFilter & "Trustee = " & Me.cboTrustee  & " And "    ' if numeric
End if

If  Me.Order_Complete = 0 Then
strFilter =   strFilter &  "Order_Complete = 0  And "
Elseif Me.Order_Complete = -1
strFilter =   strFilter &  "Order_Complete = -1  And "
end if

If strFilter <>"" then
strFilter = left(strFilter,Len(strFilter)-4)
End If

 Me.Filter = strFilter
 Me.FilterOn = True	
	
		
End Sub

You might also want to consider putting the code in its own sub - Private sub MyFilter()
then you can call it from both the combo afterupdate and the checkbox afterupdate.
 
Last edited:
Gasman said:
I think you will need to add the strings
I'm surprised it works at all as strfilter is not set?
I'd also check your string is formatted as you think it is, wouldn't you just want
Order_Complete =0 ?

HTH
Code: 
strFilter = "Trustee = """ & Me.cboTrustee & """"
strFilter = strFilter & " AND Order_Complete = """ & Me.Order_Complete = 0 & """"
Click to expand...


Hi,

I have tried the below but it blocks any data from being shown? I am guessing I have messed it up along the way :o

Code: 
Dim strFilter As String

If IsNull(Me.cboTrustee) Then
        Me.FilterOn = False
    Else
        
        strFilter = "Trustee = """ & Me.cboTrustee & """"
        strFilter = strFilter & " And Order_Complete = """ & Me.Order_Complete = 0 & """"

Me.Filter = strFilter
        Me.FilterOn = True
    End If
 
Code: 
strFilter = "Trustee = """ & Me.cboTrustee & """"

what datatype is cboTrustee? is it numeric or a string?

Code: 
AND Order_Complete = """ & Me.Order_Complete = 0 & """"
if this is a checkbox with true/false datatype then you should have
Code: 
"AND Order_Complete = " & Me.Order_Complete
 
moke123 said:
Code: 
strFilter = "Trustee = """ & Me.cboTrustee & """"
what datatype is cboTrustee? is it numeric or a string?

Code: 
AND Order_Complete = """ & Me.Order_Complete = 0 & """"
if this is a checkbox with true/false datatype then you should have
Code: 
"AND Order_Complete = " & Me.Order_Complete
Click to expand...

Hi cboTrustee is a String

It is indeed a checkbox with true/false datatype - I was under the impression that these were recorded as 0 for false and 1 for true ?

I have the 1st filter working off of the cbo again which is great - however the 2nd filter is not doing anything. I would assume because I have not passed it a value to filter ?

I have tried to add in a value but to no avail, thank you for your patience and help thus far :)

This is my code currently
Code: 
Private Sub cboTrustee_AfterUpdate()
Dim strFilter As String

If IsNull(Me.cboTrustee) Then
        Me.FilterOn = False
    Else
        
        strFilter = "Trustee = """ & Me.cboTrustee & """"
        strFilter = strFilter & " And Order_Complete = " & Me.Order_Complete

        Me.Filter = strFilter
        Me.FilterOn = True
    End If
 
Order_Complete = False

then add

debug.print strFilter
or
MSGBOX strFilter

to see what is produced
 
did the code I posted not work?

to break it down:

Code: 
If not IsNull(Me.cboTrustee) Then
strFilter = strFilter & "Trustee = """ & Me.cboTrustee & """ And "  ' if string
End if
This code will produce a string - Trustee = "John" And

Code: 
If  Me.Order_Complete = 0 Then
strFilter =   strFilter &  "Order_Complete = 0  And "
Elseif Me.Order_Complete = -1
strFilter =   strFilter &  "Order_Complete = -1  And "
end if
this code will produce a string by adding the first part to - "Order_Complete = 0 And " (or = -1)
giving you a string - Trustee = "John" And Order_Complete = 0 And

you then trim off the traiing And with
Code: 
If strFilter <>"" then
strFilter = left(strFilter,Len(strFilter)-4)
End If

which leaves the filter as - Trustee = "John" And Order_Complete = 0 And

You can add in some Debug.Print strFilter after each section to show you what it actually resolves to and adjust it from there.
 
Gasman said:
Order_Complete = False

then add

debug.print strFilter
or
MSGBOX strFilter

to see what is produced
Click to expand...

Hi,

debug.print strFilter returns nothing that I can see?

MSGBOX returns "False"
 
moke123 said:
did the code I posted not work?

to break it down:

Code: 
If not IsNull(Me.cboTrustee) Then
strFilter = strFilter & "Trustee = """ & Me.cboTrustee & """ And "  ' if string
End if
This code will produce a string - Trustee = "John" And

Code: 
If  Me.Order_Complete = 0 Then
strFilter =   strFilter &  "Order_Complete = 0  And "
Elseif Me.Order_Complete = -1
strFilter =   strFilter &  "Order_Complete = -1  And "
end if
this code will produce a string by adding the first part to - "Order_Complete = 0 And " (or = -1)
giving you a string - Trustee = "John" And Order_Complete = 0 And

you then trim off the traiing And with
Code: 
If strFilter <>"" then
strFilter = left(strFilter,Len(strFilter)-4)
End If
which leaves the filter as - Trustee = "John" And Order_Complete = 0 And

You can add in some Debug.Print strFilter after each section to show you what it actually resolves to and adjust it from there.
Click to expand...

Hi,

Apologies - I was posting from my phone and I missed your response.

I get a syntax error with

Code: 
Elseif Me.Order_Complete = -1

Many thanks
Demo
 
demogorgan said:
Hi,

Apologies - I was posting from my phone and I missed your response.

I get a syntax error with

Code: 
ElseIf Me.Order_Complete = -1

Many thanks
Demo
Click to expand...

It should be
Code: 
Elseif Me.Order_Complete = -1 [B]Then[/B]

but you can just replace the line with Else
 
moke123 said:
did the code I posted not work?

to break it down:

Code: 
If not IsNull(Me.cboTrustee) Then
strFilter = strFilter & "Trustee = """ & Me.cboTrustee & """ And "  ' if string
End if
This code will produce a string - Trustee = "John" And

Code: 
If  Me.Order_Complete = 0 Then
strFilter =   strFilter &  "Order_Complete = 0  And "
Elseif Me.Order_Complete = -1
strFilter =   strFilter &  "Order_Complete = -1  And "
end if
this code will produce a string by adding the first part to - "Order_Complete = 0 And " (or = -1)
giving you a string - Trustee = "John" And Order_Complete = 0 And

you then trim off the traiing And with
Code: 
If strFilter <>"" then
strFilter = left(strFilter,Len(strFilter)-4)
End If
which leaves the filter as - Trustee = "John" And Order_Complete = 0 And

You can add in some Debug.Print strFilter after each section to show you what it actually resolves to and adjust it from there.
Click to expand...

Hi,

Thanks to the assistance of yourself and others, this is now working :) - I did have to change the "-1" to "1" to get it to work but now it does what I need.

thank you very much for your time and the breakdown explaining it that really helped, I am slowly getting my head around access :D
 
You must log in or register to reply here.

Similar threads

N
Solved How to create new record from filtered record
2
Replies
33
Views
587
nector
N
T
Solved Filter form after update with code
Replies
9
Views
507
arnelgp
arnelgp
G
Open Filter Dialog box
Replies
12
Views
598
LarryE
LarryE
A
Filter Records
Replies
1
Views
254
June7
June7
I
Make Table from filtered subform
Replies
4
Views
360
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom