Filter By Multiple Comboboxes on a form

[Tubbzuk]

i will try, just not sure where to put the format statement.
I have put the const conjetdate =.... in after the declaration of the variables.

the line for building the string with the dates is
If Me.dateFrom <> "" Then strwhere = strwhere & "([SampleDate] BETWEEN #" & Me.dateFrom.Value & "# AND #" & Me.dateTo.Value & "#) AND "
woudl the format statement come before this or be included in part of this?

 

[Tubbzuk]

just looking at evanark's code and i think it would be somethign like
If Me.dateFrom <> "" Then strwhere = strwhere & "([SampleDate] BETWEEN #" & format(Me.dateFrom.Value, conjetdate) & "# AND #" & format(Me.dateTo.Value, conhetdate) & "#) AND "

will try and test this now as putting it in a seperate statement before hand just come back with an expected expression error.

thanks for all your help guys

 

[Isskint]

I have not had a chance to duplicate this approach, but just replace;

If Me.dateFrom <> "" Then strwhere = strwhere & "([SampleDate] BETWEEN #" & Me.dateFrom.Value & "# AND #" & Me.dateTo.Value & "#) AND "

with

If Me.dateFrom <> "" Then strwhere = strwhere & "([SampleDate] BETWEEN " & Format(Me.dateFrom, conJetDate) & " AND " & Format(Me.dateTo,conJetDate) & ") AND "

 

[Tubbzuk]

after a small amoutn of fiddling whilst adding the format statement it included an extra set of #. i have removed these and saved and re opened and tested and this worked great. it returns only values between the 2 dates selected.

Thanks for all your help guys. Thats perfect. woudl have taken weeks and alot of reading to get there ;-)

 

[Tubbzuk]

Hello everyone, new problem today.

I have added a combo box for bin number to the report form. This is called cbobin.
the sql statement comes out as
SELECT [QC Tasting].* FROM [QC Tasting]WHERE (([Bin Number]='24'));
but this throws a error 3464 type mismatch.
Cant see why as this works fine for things like the product type.
the code is
If Me.cboBin.ListIndex > -1 Then strwhere = strwhere & "([Bin Number]='" & Me.cboBin & "') AND "

Is this because it is numeric?

thanks for your help,

regards,

Alan

 

[Tubbzuk]

Hi,
I have resolved the last question. I just changed the field type to Text in the database and then set the field validation to numeric only.

Not the best wy but it works for me ;-)

 

[KiEESH]

Working on a similar issue.

Trying to keep it simple. How can this existing code be modified to retain the "Phase" filter and then further widdle down on "Zone?"

Private Sub cmb_Phase_AfterUpdate()
 
    If IsNull(Me.cmb_Phase) Then
        Me.FilterOn = False
    Else
        Me.Filter = "Phase = """ & Me.cmb_Phase & """"
        Me.FilterOn = True
    End If
    
End Sub
 
 
Private Sub lst_Zones_AfterUpdate()
 
    If IsNull(Me.lst_Zones) Then
        Me.FilterOn = False
    Else
        Me.Filter = "Zone = """ & Me.lst_Zones & """"
        Me.FilterOn = True
    End If
    
End Sub

 

[KiEESH]

As of right now, this code works to filters on either one OR the other field.

I need "AND."

Just not sure how to make the two play nice together.

 

[Isskint]

Combine the two subs into one, and call that sub from the AfterUpdate events, like

Private Sub cmb_Phase_AfterUpdate()
 
   SetMyFilter
    
End Sub
 
 
Private Sub lst_Zones_AfterUpdate()
 
   SetMyFilter
    
End Sub
Public Sub SetMyFilter()

Dim strFilter As String

strFilter = ""

    If Not IsNull(Me.cmb_Phase) Then strFilter = "Phase = """ & Me.cmb_Phase & """"

    If Not IsNull(Me.lst_Zones) Then strFilter = " AND Zone = """ & Me.lst_Zones & """"
    
    If Left(strFilter, 3) = "AND" Then strFilter = Right(strFilter, Len(strFilter) - 4)
    
    If strFilter <> "" Then
        Me.Filter = strFilter
        Me.Filteron = True
    End If
End Sub

 

[KiEESH]

Got an error message:

Run-Time Error: '3075'

Syntax error (missing operator) in query expression 'ZONE = "" & Me.lst_Zones &"'

Debugger points to:

Me.Filter = strFilter

 

[Isskint]

Sorry, do not have Access on my laptop so could not sense check the code. replace the following 2 lines

    If Not IsNull(Me.lst_Zones) Then strFilter =[COLOR="Red"]strFilter &[/COLOR] " AND Zone = '" & Me.lst_Zones & "'"
    
    If Left(strFilter, [COLOR="red"]4) = " AND" [/COLOR]Then strFilter = Right(strFilter, Len(strFilter) - 4)

 

[KiEESH]

That eliminates the bug.

But also brings me back to my first issue:

I need to filter on Phase and Zone. Not Phase or Zone.

Your code results in an or option.


Any way to make it 'and?'

 

[Isskint]

What do you suppose the AND in the returned filter string does???

The string will return a filter criteria that says;

Me.cmb_Phase value AND Me.lst_Zones value IF both of those are NOT NULL. If only one of those is NOT NULL then it will only return a filter on that controls value.

 

[KiEESH]

I assumed the AND would function as you say it should.

But, it doesn't. It functions as an OR.


Here's my entire code for the form.
Not sure if something else in here is causing the issue:

Option Compare Database

Private Sub Form_Load()
Me.lst_Zones.Enabled = True
Me.cmb_Phase.Enabled = True
Me.cmb_Phase = Null
Me.lst_Zones = Null
Me.FilterOn = False
DoCmd.Maximize
DoCmd.Close acForm, "SWITCHBOARD", acSaveNo

End Sub
 
Private Sub Form_Current()
Me.chk_Verify = Null
Me.cmb_Phase = Null
Me.lst_Zones = Null
End Sub
 
Private Sub chk_Verify_Click()
Dim verifyAns As String
If Me.chk_Verify = True Then
    Me.ENG_DATA_VER_EMPL = f0SUserName()
Else
    Me.ENG_DATA_VER_EMPL = ""
End If

verifyAns = MsgBox("Record Verified. Review Next Record?", vbYesNo)
If verifyAns = vbNo Then
          
    DoCmd.Close acForm, "frm_Verify", acSaveYes
    DoCmd.OpenForm "SWITCHBOARD"
                
Else
        
If verifyAns = vbYes Then
    'To check if it is possible to go to next record
    If Me.CurrentRecord < Me.Recordset.RecordCount Then
        
        DoCmd.GoToRecord , , acNext
    
    Else
    
    MsgBox "End of Records."
    
    End If
    
 End If
 End If
End Sub
 

Private Sub cmb_Phase_AfterUpdate()
 
   SetMyFilter
    
End Sub
 
 
Private Sub lst_Zones_AfterUpdate()
 
   SetMyFilter
    
End Sub
 

Public Sub SetMyFilter()
Dim strFilter As String
strFilter = ""
    If Not IsNull(Me.cmb_Phase) Then strFilter = "[Phase] = """ & Me.cmb_Phase & """"
    If Not IsNull(Me.lst_Zones) Then strFilter = strFilter & " AND Zone = '" & Me.lst_Zones & "'"
    If Left(strFilter, 4) = " AND" Then strFilter = Right(strFilter, Len(strFilter) - 4)
    If strFilter <> "" Then
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
End Sub
 
Private Sub btn_ResetFilter_Click()
Me.FilterOn = False
Me.cmb_Phase = Null
Me.lst_Zones = Null
End Sub
 
Private Sub btn_Exit_Click()
Dim saveAns As String
saveAns = MsgBox("Exit without saving?", vbYesNo)
   
    If saveAns = vbNo Then
    
       
      Cancel = True
        
    Else
        
      If saveAns = vbYes Then
          Me.Undo
          DoCmd.Close acForm, "frm_Verify", acSaveNo
          DoCmd.OpenForm "SWITCHBOARD"
          
        
      Else
      End If
    End If
End Sub

The code you suggested works just as my original code did (OR):

'Private Sub cmb_Phase_AfterUpdate()
    'If IsNull(Me.cmb_Phase) Then
        'Me.FilterOn = False
    'Else
        'Me.Filter = "Phase = """ & Me.cmb_Phase & """"
        'Me.FilterOn = True
    'End If
    
'End Sub

'Private Sub lst_Zones_AfterUpdate()
    'If IsNull(Me.lst_Zones) Then
        'Me.FilterOn = False
    'Else
        'Me.Filter = "Zone = """ & Me.lst_Zones & """"
        'Me.FilterOn = True
    'End If
    
    
'End Sub

 

[KiEESH]

I was sure your code was right. Didn't mean to offend.

Was confused b/c I took the wrong approach in reseting the filters in other events.

Commented out the 'null' commands in those events and filters work fine.


Thanks much. You've been extremely helpful!