Checkbox Filter?

[82280zx]

I hate to bother you guys again and I know this should be simple to do (I think) but my brain seems to not be functioning at the moment.

I'm trying to make a checkmark filter, I want to filter my query / form to only show the data that has a date in the retired date field. And when you uncheck the checkbox I want it to show everything that doesn't have a retired date and hide the ones that do have a retired date.

I know I need if a if statement, what I've got so far is this.
Private Sub chkRetired_AfterUpdate()
If Me.chkRetired = True Then
strFilter = Me.Filter

strFilter = strFilter & " And Not IsNull([DateRetired])"
Me.Filter = strFilter
Me.FilterOn = True
Else
strFilter = Me.Filter

strFilter = strFilter & ""
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub

[JeroenMioch]

Private Sub chkRetired_Click()

    Dim strFilter As String
    Dim blnFilter As Boolean
    
    blnFilter = False
    
    strFilter = " 1=1 "
        strFilter = strFilter + " and Dateretired <> 0"
        blnFilter = True
    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
   
    Me.FilterOn = True

End Sub

This example works in my database.

[82280zx]

I tried what you put here but it still seems to be doing the same thing, it does filter I think but when I uncheck the text box it doesn't go back to the results without a retirement date.

[JeroenMioch]

Im not sure what access does when checkmarking something and in the same code checking if the checkmark is checked, you follow me ?
I cant test it because im not at work but try this :

Private Sub chkRetired_Click()
 
If me.chkRetired = true then
 
    Dim strFilter As String
    Dim blnFilter As Boolean
   
    blnFilter = False
   
    strFilter = " 1=1 "
        strFilter = strFilter + " and Dateretired <> 0"
        blnFilter = True
    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
  
    Me.FilterOn = True
 
else
 
    Dim strFilter As String
    Dim blnFilter As Boolean
   
    blnFilter = False
   
    strFilter = " 1=1 "
        strFilter = strFilter + " and isNull(Dateretired) "
        blnFilter = True
    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
 
End if
End Sub

[82280zx]

Im not sure what access does when checkmarking something and in the same code checking if the checkmark is checked, you follow me ?
I cant test it because im not at work but try this :

Private Sub chkRetired_Click()
 
If me.chkRetired = true then
 
    Dim strFilter As String
    Dim blnFilter As Boolean
   
    blnFilter = False
   
    strFilter = " 1=1 "
        strFilter = strFilter + " and Dateretired <> 0"
        blnFilter = True
    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
  
    Me.FilterOn = True
 
else
 
    Dim strFilter As String
    Dim blnFilter As Boolean
   
    blnFilter = False
   
    strFilter = " 1=1 "
        strFilter = strFilter + " and isNull(Dateretired) "
        blnFilter = True
    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
 
End if
End Sub

I changed what you had a little bit but it did work thank you! This is what I changed it to, now I need to figure out how to tie it into my other filter on my search but you got me started thanks for the help!

Private Sub chkRetired_Click()
Dim strFilter As String
Dim blnFilter As Boolean

If Me.chkRetired = True Then

blnFilter = False

strFilter = " 1=1 "
strFilter = strFilter + " and isNull(DateRetired) "
blnFilter = True
If blnFilter Then
Me.Filter = strFilter
Else
Me.Filter = "1=2"
End If
Me.FilterOn = True

Else

blnFilter = False

strFilter = " 1=1 "
strFilter = strFilter + " and DateRetired <> 0"
blnFilter = True
If blnFilter Then
Me.Filter = strFilter
Else
Me.Filter = "1=2"
End If
Me.FilterOn = True

End If

End Sub

[JeroenMioch]

Good to hear mate

With the filter you now have you can look for any search criteria in your records

Just make a button (Search) and include all of your search criteria to the code like this :

If Me.fldSearchLastName <> "" Then
strFilter = strFilter & "[LastName] LIKE '*" & Me.fldSearchLastName.Value & "*'"
blnFilter = True
End If

[82280zx]

Good to hear mate

With the filter you now have you can look for any search criteria in your records

Just make a button (Search) and include all of your search criteria to the code like this :

If Me.fldSearchLastName <> "" Then
strFilter = strFilter & "[LastName] LIKE '*" & Me.fldSearchLastName.Value & "*'"
blnFilter = True
End If

The filter you gave me works for what I want with on of my forms I've set up but now I'm trying to filter a query I've made, but I'm running into a wall again.

I know I can put Is Null in the criteria under DateRetired in my query to only show the data that doesn't have a date but I want to be able to toggle that with a checkbox, I tried to make a if statement to pass the value "Is Null" to a textbox then I put the textbox into the query so it would hopefully filter by whats in the textbox but it errors out and says it's to complex.

[82280zx]

Good to hear mate

With the filter you now have you can look for any search criteria in your records

Just make a button (Search) and include all of your search criteria to the code like this :

If Me.fldSearchLastName <> "" Then
strFilter = strFilter & "[LastName] LIKE '*" & Me.fldSearchLastName.Value & "*'"
blnFilter = True
End If

Sorry it's been crazy these last few days, thanks for your help on this JeroenMioch. I have a filter I use like this at the moment,

Private Sub txtSearch_Change()

SearchAssetsSubform.Form.Filter = "[Room] & '|' & [Elcid in DCMS] & '|' & [ProductType] & '|' & [Manufacturer] & '|' & [Department] & '|' & [Agency] & '|' & [Barcode] & '|' & [GridLocation] & '|' & [Model] & '|' & [StateAssetNumber] & '|' & [SerialNumberorDellServiceTag] & '|' & [HostName] like '*" & txtSearch.Text & "*'"
SearchAssetsSubform.Form.FilterOn = True

End Sub

If I have the code up in the checkbox on click
Private Sub chkRetired_Click()
Dim strFilter As String
Dim blnFilter As Boolean

If Me.chkRetired = True Then

blnFilter = False

strFilter = " 1=1 "
strFilter = strFilter + " and isNull(DateRetired) "
blnFilter = True
If blnFilter Then
Me.Filter = strFilter
Else
Me.Filter = "1=2"
End If
Me.FilterOn = True

Else
blnFilter = False

strFilter = " 1=1 "
strFilter = strFilter + " and DateRetired <> 0"
blnFilter = True
If blnFilter Then
Me.Filter = strFilter
Else
Me.Filter = "1=2"
End If
Me.FilterOn = True

End If
End Sub

Can I still call strFilter in the other search box? or how do I pass it to this function?

Thanks again!