Autofilter date range not showing correct results

[skull_eagle]

I'm running the attached code, it is applying the correct filter on the correct column but the results displayed are incorrect.

When I manually apply the same filter, the correct results are displayed.

This is very strange and I don't understand why this is happening.
Any help would be greatly appreciated.
Thank you

DateVal = Worksheets("Admin").Range("P6")

If ColNum2 = 99 Then
    
If Search_Form.Controls("CM_" & ColNum2) = "< 35" Then
  
        
    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6, _
    Criteria1:="<" & DateVal, Operator:=xlAnd
        
ElseIf Search_Form.Controls("CM_" & ColNum2) = "> 35" Then

    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6, _
    Criteria1:=">" & DateVal, Operator:=xlAnd

ElseIf Search_Form.Controls("CM_" & ColNum2) = "CLEAR" Then

    ActiveSheet.ListObjects("Table_RawData").Range.AutoFilter Field:=6
    

        
End If
End If

 

[AlphaFrog]

This is a bit of a guess, but I think when you concatenate the dateval with a string operator "<", the date becomes a string and loses its serial date value.

Try converting the dateval to a long-type numeric value then concatenating the operator.

Criteria1:="<" & Clng(DateVal)

 

[skull_eagle]

This is a bit of a guess, but I think when you concatenate the dateval with a string operator "<", the date becomes a string and loses its serial date value.

Try converting the dateval to a long-type numeric value then concatenating the operator.

Criteria1:="<" & Clng(DateVal)

Thank you so much!!!

It works perfectly.

 

[Herakles]

I had this same problem last week and it took a while for me to realise that dates are stored as a long type.

I'll never forget that.