Filtering query by dates

[FishVal]

No wonder it filters date as string as long as you use string criteria.
Try to change
"12/31" & "/" & [Enter the Year]
to
DateSerial([Enter the Year], 12, 31).
or
CDate("12/31" & "/" & [Enter the Year])
as Scott have suggested

Additionally:
"n/a" 's returned with Switch expression are expected cause "Type mismatch" error
Possible solutions:
1. Make Switch expression to return Null instead of "n/a"
2. VBA code where error can be trapped or prevented. Like this.

Public Function IsYearLessOrEqual(ByVal dteInput As Variant, _
                                  ByVal lngYear As Variant) As Boolean

    IsYearLessOrEqual = False
    On Error GoTo Exit_IsYearLessOrEqual
    If Year(dteInput) <= lngYear Then IsYearLessOrEqual = True
    
Exit_IsYearLessOrEqual:

End Function

[mlcampeau]

No wonder it filters date as string as long as you use string criteria.
Try to change
"12/31" & "/" & [Enter the Year]
to
DateSerial([Enter the Year], 12, 31).
or
CDate("12/31" & "/" & [Enter the Year])
as Scott have suggested

Additionally:
"n/a" 's returned with Switch expression are expected cause "Type mismatch" error
Possible solutions:
1. Make Switch expression to return Null instead of "n/a"
2. VBA code where error can be trapped or prevented. Like this.

Public Function IsYearLessOrEqual(ByVal dteInput As Variant, _
                                  ByVal lngYear As Variant) As Boolean

    IsYearLessOrEqual = False
    On Error GoTo Exit_IsYearLessOrEqual
    If Year(dteInput) <= lngYear Then IsYearLessOrEqual = True
    
Exit_IsYearLessOrEqual:

End Function

I knew it had to be something simple! I changed my criteria to:
DateSerial([Enter the Year], 12, 31) and that works great! Thanks so much, both of you, for your help!

[FishVal]

Happy coding.
And good luck in the rest of your project(s).

Best regards,
Fish

[Scott Price]

Glad you got it worked out!

Happy coding :-)

Regards,
Scott