Solved Not equal to filter in VBA function

[JamieRhysEdwards]

Hi All,

I have a check box labelled "Show Retired" which when ticked, I want it to show assets labelled "Retired" and when it's unchecked, to hide them. I cannot quite figure out how this is done however.

This is what I've done so far:

Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "[AssetCondition] <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub

If I apply the filter manually by tapping the down arrow on the column and then deselecting Retired, it works as expected. If I then check Show Retired, it removes the filter as expected.

However, if I then disable (uncheck) Show Retired, I then get a 3464 error which states it's a Data type mismatch in criteria expression.

If I then tap debug, it shows Me.FilterOn = False is the culprit?

 

[arnelgp]

you may try:

Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "Nz([AssetCondition], '@') <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub

 

[JamieRhysEdwards]

you may try:

Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "Nz([AssetCondition], '@') <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub

This doesn't seem to do anything... It's fixed the mismatch error but nothing is actually applied

 

[arnelgp]

probably Moved your code to the AfterUpdate event of the checkbox, not the Click event.

 

[JamieRhysEdwards]

Hi @arnelgp, I've moved it to AfterUpdate() and it's having the same effect.

 

[Eugene-LS]

and it's having the same effect.

Please specify the format of the [AssetCondition] field
Is it exactly textual?

 

[JamieRhysEdwards]

@Eugene-LS Sorry for the very late reply. It's coming from a lookup table if this helps, so it can either be grabbed textually, or if preferred, an ID value of 4

 

[GPGeorge]

@Eugene-LS Sorry for the very late reply. It's coming from a lookup table if this helps, so it can either be grabbed textually, or if preferred, an ID value of 4

No, it's not an either or choice. It's going to be the ID, or Primary Key value, that matters.

 

[Cronk]

Try

Private Sub chkShowRetired_Click()
   If chkShowRetired.Value = True Then 
       Me.filter = "[AssetCondition] = 'Retired'"      ' <<<<Missing
       Me.FilterOn = True
  Else
      Me.Filter = "[AssetCondition] <> 'Retired'"
      Me.FilterOn = True
  End If
End Sub

 

[Pat Hartman]

If the AssetCondition is a table level lookup, you need to use the ID in this if statement. OR, you need to modify the RecordSource query to join to the lookup table to bring in the text value. Jamie did say it was a table level lookup.

 

[JamieRhysEdwards]

Thanks for the support all! I think I've figured it out (might not be the most efficient way of doing it and I'll soon see). This is how I'm doing it at present:

Private Sub Form_Load()
    ...
    TempVars("AssetList_ShowRetired") = False
    ...
End Sub

Private Sub chkShowRetired_Click()
    If (Form!chkShowRetired) Then
        TempVars("AssetList_ShowRetired") = True
        ToggleFilters
    Else
        TempVars("AssetList_ShowRetired") = False
        ToggleFilters
    End If
End Sub

Private Function ToggleFilters()
    DoCmd.ShowAllRecords
    
    If (Not TempVars("AssetList_ShowRetired")) Then
        ' Filter out Retired assets if checkbox is cleared
        DoCmd.ApplyFilter "", Eval("""RetiredDate is null or RetiredDate > date()"""), ""
    End If
End Function