Filters and VBA

M

Menes

Registered User.
Local time
Today, 23:44
Joined
Sep 21, 2007
Messages
16
I've created a form that allows me to select the email addresses that I want from the database based on the dataset that the customers has signed up for.

I now need to put another filter on this so that as well selecting which dataset the customer has signed up, but it also selects the type of customer they are. Eg provider, commissioner, Third Party etc so that the emails are sent to the appropriate customers. For example, the provider email may contain information that isn't relevent to commissioners so there isn't any point in sending to them so I want to filter them out.

I've included the code that allows me to select datasets, sometmes we will want to send the same email to customers of two or more datasets.

I've commented out the category section at the moment as it isn't working and is returning just based on dataset at the moment and ignores the additional filter.

How do I modify the code to include this secondary filter?

Many thanks for reading this and any help provided will be much appreciated.

Code: 
Option Compare Database
Option Explicit
Private Sub DistroClear_Click()
    Dim intIndex As Integer
 
    ' Clear all search items
    Me.cmbMHMDS = ""
    Me.cmbIAPT = ""
    Me.cmbChild = ""
    Me.cmbComm = ""
    Me.cmbCAMHS = ""
    Me.cmbYPEOPLE = ""
    Me.cmbKey = ""
 
    ' Reset
    If BuildFilter = "" Then
        Me.frmDistroSub.Form.RecordSource = "SELECT * FROM qryDistro " & BuildFilter
    Else
        Me.frmDistroSub.Form.RecordSource = "SELECT * FROM qryDistro WHERE " & BuildFilter
    End If
 
End Sub
Private Sub DistroSearch_Click()
 
   ' Update the record source
    If BuildFilter = "" Then
        Me.frmDistroSub.Form.RecordSource = "SELECT * FROM qryDistro " & BuildFilter
    Else
        Me.frmDistroSub.Form.RecordSource = "SELECT * FROM qryDistro WHERE " & BuildFilter
    End If
 
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
 
    ' Check for MHMDS
    If Me.cmbMHMDS = -1 Then
        varWhere = varWhere & "([DS_MHMDS] = True) OR "
    ElseIf Me.cmbMHMDS = 0 Then
        varWhere = varWhere & "([DS_MHMDS] = False) OR "
    End If
    ' Check for IAPT
    If Me.cmbIAPT = -1 Then
        varWhere = varWhere & "([DS_IAPT] = True) OR "
    ElseIf Me.cmbIAPT = 0 Then
        varWhere = varWhere & "([DS_IAPT] = False) OR "
    End If
    ' Check for Children
    If Me.cmbChild = -1 Then
        varWhere = varWhere & "([DS_CHILDREN] = True) OR "
    ElseIf Me.cmbChild = 0 Then
        varWhere = varWhere & "([DS_CHILDREN] = False) OR "
    End If
     ' Check for Community
    If Me.cmbComm = -1 Then
        varWhere = varWhere & "([DS_COMMUNITY] = True) OR "
    ElseIf Me.cmbComm = 0 Then
        varWhere = varWhere & "([DS_COMMUNITY] = False) OR "
    End If
    ' Check for CAMHS
    If Me.cmbCAMHS = -1 Then
        varWhere = varWhere & "([DS_CAMHS] = True) OR "
    ElseIf Me.cmbCAMHS = 0 Then
        varWhere = varWhere & "([DS_CAMHS] = False) OR "
    End If
 
        ' Check for Young People
    If Me.cmbYPEOPLE = -1 Then
        varWhere = varWhere & "([DS_YPEOPLE] = True) OR "
    ElseIf Me.cmbYPEOPLE = 0 Then
        varWhere = varWhere & "([DS_YPEOPLE] = False) OR "
    End If
 
 '       ' Check for Category
 '   If Not IsNull(Me.cmbCategory) Then
 '       varWhere = varWhere & "([Category] = """ & Me.cmbCategory & """) AND "
 '   End If
 '
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
 
    Else
        ' strip off last "OR" in the filter
        If Right(varWhere, 4) = " OR " Then
            varWhere = Left(varWhere, Len(varWhere) - 4)
        End If
 
    End If
 
    BuildFilter = varWhere
End Function
 
Private Sub ExitDistro_Click()
On Error GoTo Err_Exitsearch_Click
    DoCmd.Close
Exit_Exitsearch_Click:
    Exit Sub
Err_Exitsearch_Click:
    MsgBox Err.Description
    Resume Exit_Exitsearch_Click
 
End Sub
 
You use "OR" in the build of your where critera and put an "AND" on the wrong place. Your code should be something like (untested).
Code: 
     ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        ' strip off last "OR" in the filter
'        If Right(varWhere, 4) = " OR " Then 'Useless there's always a "OR" here
            varWhere = Left(varWhere, Len(varWhere) - 4)
            varWhere = "(" & varWhere & ") AND " 'put the or statement between ()
'        End If 'Removed, see above
    End If

            ' Check for Category
    If Not IsNull(Me.cmbCategory) Then
       varWhere = "([Category] = '" & Me.cmbCategory & "')" 'Assuming category is a string
    End If
 
    If Right(varWhere, 5) = " AND " Then 
           varWhere = Left(varWhere, Len(varWhere) - 5)
 
    End If
 
PeterF,

Many thanks for this and sorry I haven't posted a reply in thanks. I've adjusted the code now and it's almost there.

What the form is doing now though is only running the query using the category and so brings everything based on that and ignores the initial dataset request meaning that I get, for exampal, all site contacts for the whole database rather then site contacts for IAPT and MHMDS which I specify first.

I'll play around with the code and see if I can sort this out as it runs further then it did before.

Many thanks for your help.
 
I've played about with this now and still seem to be missing some key command. If I take the first part of the filter the section:
Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
 
    ' Check for MHMDS
    If Me.cmbMHMDS = -1 Then
        varWhere = varWhere & "([DS_MHMDS] = True) OR "
    ElseIf Me.cmbMHMDS = 0 Then
        varWhere = varWhere & "([DS_MHMDS] = False) OR "
    End If
    ' Check for IAPT
    If Me.cmbIAPT = -1 Then
        varWhere = varWhere & "([DS_IAPT] = True) OR "
    ElseIf Me.cmbIAPT = 0 Then
        varWhere = varWhere & "([DS_IAPT] = False) OR "
    End If
    ' Check for Children
    If Me.cmbChild = -1 Then
        varWhere = varWhere & "([DS_CHILDREN] = True) OR "
    ElseIf Me.cmbChild = 0 Then
        varWhere = varWhere & "([DS_CHILDREN] = False) OR "
    End If
     ' Check for Community
    If Me.cmbComm = -1 Then
        varWhere = varWhere & "([DS_COMMUNITY] = True) OR "
    ElseIf Me.cmbComm = 0 Then
        varWhere = varWhere & "([DS_COMMUNITY] = False) OR "
    End If
    ' Check for CAMHS
    If Me.cmbCAMHS = -1 Then
        varWhere = varWhere & "([DS_CAMHS] = True) OR "
    ElseIf Me.cmbCAMHS = 0 Then
        varWhere = varWhere & "([DS_CAMHS] = False) OR "
    End If
 
        ' Check for Young People
    If Me.cmbYPEOPLE = -1 Then
        varWhere = varWhere & "([DS_YPEOPLE] = True) OR "
    ElseIf Me.cmbYPEOPLE = 0 Then
        varWhere = varWhere & "([DS_YPEOPLE] = False) OR "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
 
    Else
        ' strip off last "OR" in the filter
        If Right(varWhere, 4) = " OR " Then
            varWhere = Left(varWhere, Len(varWhere) - 4)
        End If
 
    End If
 
    BuildFilter = varWhere
End Function

this will return the correct rcords. For example, if the check boxes for MHMDS and IAPT are ticked, it will bring back all records with at least one of those two ticked.

However, when I add the second part to do with category and the query to changes to look like:

Code: 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
 
 ' Check for MHMDS
    If Me.cmbMHMDS = -1 Then
        varWhere = varWhere & "([DS_MHMDS] = True) OR "
    ElseIf Me.cmbMHMDS = 0 Then
        varWhere = varWhere & "([DS_MHMDS] = False) OR "
    End If
 
    ' Check for IAPT
    If Me.cmbIAPT = -1 Then
        varWhere = varWhere & "([DS_IAPT] = True) OR "
    ElseIf Me.cmbIAPT = 0 Then
        varWhere = varWhere & "([DS_IAPT] = False) OR "
    End If
 
    ' Check for Children
    If Me.cmbChild = -1 Then
        varWhere = varWhere & "([DS_CHILDREN] = True) OR "
    ElseIf Me.cmbChild = 0 Then
        varWhere = varWhere & "([DS_CHILDREN] = False) OR "
    End If
 
     ' Check for Community
    If Me.cmbComm = -1 Then
        varWhere = varWhere & "([DS_COMMUNITY] = True) OR "
    ElseIf Me.cmbComm = 0 Then
        varWhere = varWhere & "([DS_COMMUNITY] = False) OR "
    End If
 
    ' Check for CAMHS
    If Me.cmbCAMHS = -1 Then
        varWhere = varWhere & "([DS_CAMHS] = True) OR "
    ElseIf Me.cmbCAMHS = 0 Then
        varWhere = varWhere & "([DS_CAMHS] = False) OR "
    End If
 
        ' Check for Young People
    If Me.cmbYPEOPLE = -1 Then
        varWhere = varWhere & "([DS_YPEOPLE] = True) OR "
    ElseIf Me.cmbYPEOPLE = 0 Then
        varWhere = varWhere & "([DS_YPEOPLE] = False) OR "
    End If
    ' Check if there is a filter to return...
   If IsNull(varWhere) Then
       varWhere = ""
   Else
       ' strip off last "OR" in the filter
'        If Right(varWhere, 4) = " OR " Then 'Useless there's always a "OR" here
           varWhere = Left(varWhere, Len(varWhere) - 4)
           varWhere = "(" & varWhere & ") AND " 'put the or statement between ()
'        End If 'Removed, see above
   End If
           ' Check for Category
   If Not IsNull(Me.cmbCategory) Then
      varWhere = "([Category] = '" & Me.cmbCategory & "')" 'Assuming category is a string
   End If
   If Right(varWhere, 5) = " AND " Then
          varWhere = Left(varWhere, Len(varWhere) - 5)
   End If
    BuildFilter = varWhere
End Function

When I drop a category in the drop down field it brings back all commissioners regardless of which dataset when I would want the commissioner of MHMDs and IAPT.

I'm struggling to work out what needs to be changed to ensure that that the category becomes and AND part of the query and not an OR.

It's probably something really simple and easy and if any can point out the mistake or error I'd very much appreciate it.

Many thanks
 
You must log in or register to reply here.

Similar threads

oxicottin
Cant get form filter to work correctly
Replies
8
Views
526
plog
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
1,101
oxicottin
oxicottin
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
744
Faoineag
F
A
Filter Records
Replies
1
Views
390
June7
June7
mloucel
Solved SQL Help..
Replies
10
Views
592
mloucel
mloucel

Users who are viewing this thread

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