Bad code

[Oldjay]

When I run the code it doesn't return any records but if I substitute the
actual date in the criteriai it returns the correct records

sub test()
Dim birthdate As Date
Dim birthdate1 As Date
birthdate = Range("Form!C47") ' this value is 12/1/05 when you hover
over "birthdate"
birthdate1 = Range("Form!C48") ' This value is 12/31/05 when you hover
over "birthdate1"

Sheets("Records").Select
Range("J2").Select
Selection.AutoFilter

This filter returns no records
Selection.AutoFilter Field:=10, Criteria1:=">=(birthdate)",
Operator:=xlAnd, _
Criteria2:="<=(birthdate1)"

This filter returns no records
Selection.AutoFilter Field:=10, Criteria1:=">=12/1/05", Operator:=xlAnd,
_
Criteria2:="<=(12/31/05"

endsub

What I am I doing wrong?

oldjay

 

[Dave Peterson]

Dates, VBA and filters don't always play nice:

I'd try this first:

Option Explicit
Sub test()

Dim birthdate As Date
Dim birthdate1 As Date

birthdate = Worksheets("Form").Range("C47")
birthdate1 = Worksheets("Form").Range("C48")

Sheets("Records").Select
Range("J2").Select
Selection.AutoFilter

Selection.AutoFilter Field:=10, Criteria1:=">=" & birthdate, _
Operator:=xlAnd, Criteria2:="<=" & birthdate1

End Sub

if that doesn't work, try this:

Selection.AutoFilter Field:=10, Criteria1:=">=" & clng(birthdate), _
Operator:=xlAnd, Criteria2:="<=" & clng(birthdate1)

 

[Oldjay]

The first one worked
Thanks

Dates, VBA and filters don't always play nice:

I'd try this first:

Option Explicit
Sub test()

Dim birthdate As Date
Dim birthdate1 As Date

birthdate = Worksheets("Form").Range("C47")
birthdate1 = Worksheets("Form").Range("C48")

Sheets("Records").Select
Range("J2").Select
Selection.AutoFilter

Selection.AutoFilter Field:=10, Criteria1:=">=" & birthdate, _
Operator:=xlAnd, Criteria2:="<=" & birthdate1

End Sub

if that doesn't work, try this:

Selection.AutoFilter Field:=10, Criteria1:=">=" & clng(birthdate), _
Operator:=xlAnd, Criteria2:="<=" & clng(birthdate1)