Filter multiple criteria (1 Viewer)

D

demogorgan

Registered User.
Local time
Today, 03:54
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
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:54
Joined
Sep 21, 2011
Messages
14,299
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:
moke123

moke123

AWF VIP
Local time
Yesterday, 22:54
Joined
Jan 11, 2013
Messages
3,920
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:
D

demogorgan

Registered User.
Local time
Today, 03:54
Joined
Nov 29, 2017
Messages
37
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 :eek:

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
 
moke123

moke123

AWF VIP
Local time
Yesterday, 22:54
Joined
Jan 11, 2013
Messages
3,920
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
 
D

demogorgan

Registered User.
Local time
Today, 03:54
Joined
Nov 29, 2017
Messages
37
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
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:54
Joined
Sep 21, 2011
Messages
14,299
Order_Complete = False

then add

debug.print strFilter
or
MSGBOX strFilter

to see what is produced
 
moke123

moke123

AWF VIP
Local time
Yesterday, 22:54
Joined
Jan 11, 2013
Messages
3,920
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.
 
D

demogorgan

Registered User.
Local time
Today, 03:54
Joined
Nov 29, 2017
Messages
37
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"
 
D

demogorgan

Registered User.
Local time
Today, 03:54
Joined
Nov 29, 2017
Messages
37
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
 
isladogs

isladogs

MVP / VIP
Local time
Today, 03:54
Joined
Jan 14, 2017
Messages
18,221
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
 
D

demogorgan

Registered User.
Local time
Today, 03:54
Joined
Nov 29, 2017
Messages
37
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

C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
281
CraigDouglas
C
MattBaldry
Solved Open Form and Apply Filter to Subform
Replies
3
Views
90
theDBguy
theDBguy
S
Unbound combo box is clear after making selection
Replies
10
Views
159
theDBguy
theDBguy
J
Solved Conflict in code for two controls
Replies
15
Views
735
Pat Hartman
P
S
Solved Sub procedure to filter different textboxes in subform
Replies
5
Views
277
silentwolf
S

Users who are viewing this thread

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