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

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 wrote:

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

The first one worked
Thanks

"Dave Peterson" wrote:

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 wrote:

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