VBA Date Filtering Query

[NewOrderFac33]

Good afternoon,

When I use the following code to filter an Excel database to all rows where the date in the first field is less than or equal to the value in the named range "DeleteOldest"

DODCriteriaString = "<=" & Format(Sheets("Database").Range("DeleteOldest").value, "yyyy/mm/dd")
ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:=DODCriteriaString, Operator:=xlAnd

my procedure filters my database and returns data rows correctly

However, when I use a variation on the above to return rows where the date in the first field is equal to the value in the named range "DeleteNewest"

DNDCriteriaString = "=" & Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd")
ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:=DNDCriteriaString, Operator:=xlAnd

no rows are returned (although there are several)

If I change my criteria string to

DNDCriteriaString = ">=" & Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd")

it works fine, and, for the purposes of my dataset, this is also fine, as there are no rows with a date value value greater than "DeleteNewest"

So, it works with >= and <= but not = alone.

Can anyone suggest why this might be?

Thanks in advance

Pete

 

[NewOrderFac33]

I just discovered that if I change the formatting sequence for the date thus:

DNDCriteriaString = "=" & Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy")

that is from "yyyy/mm/dd" to "dd/mm/yyyy", it works with = on its own just fine.

I have NO idea why this might be, but I'm adding it as a comment to my code for future reference before I forget!

Pete

 

[NewOrderFac33]

...and for completeness, I tried both options with both formatting sequences:

For <= and >=, you have to use "yyyy/mm/dd" - "dd/mm/yyyy" won't work
For =, you have to use "dd/mm/yyyy" - "yyyy/mm/dd" won't work

Bizarre, but that's VBA for you!

Pete

 

[Fluff]

Glad you sorted it, but as a matter of interest, what happens if you remove the = sign for an exact match.

DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy")

 

[NewOrderFac33]

Hi, Fluff - thanks for taking an interest

DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd") doesn't work
DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy") does.

Cheers

Pete

 

[Fluff]

Just one of the problems with working with Dates in VBA, for those of us who don't use the US date system.