Filtering Of Date Value Not Working

[Ark68]

I'm trying to apply a filter to a date column in my database, but the results are empty. Is someone able to suggest a correction to my code?

Sub StaffOnChange()
    Dim nWSName As String
    Dim wsSS As Worksheet
    Dim changeDate As Date
    
    nWSName = ActiveSheet.Name
    Set wsSS = Worksheets(nWSName)

    changeDate = wsHold.Range("B3")

    With wsSS
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("B3").AutoFilter Field:=1, Criteria1:=changeDate
        .AutoFilterMode = False
    End With
     
    wsSS.Delete
    
End Sub

The value changeDate = 2022-07-22, is a date, and can be accurately reformatted as 22-Jul-22 ("dd-mmm-yy").
The data in column A of worksheet wsSS are dates, represented as serials of that date. They can be accurately reformatted as "dd-mmm-yy".
In my testing, with changedate = 2022-07-22, the filter is not filtering out the row (72) in which A72 = 44764 (2022-07-22).

 

[davesexcel]

Possibly you need value?

changeDate = wsHold.Range("B3").VALUE

 

[Ark68]

Hi Dave, thank you for your suggestion. Changed my code, but unfortunately still no results.
In case it matters, rows 1 and 2 are reserved for headers. After the filter, all rows of data are hidden. There are no visible cells (except the header values where there are values)

 

[Ark68]

Bump

Still perplexed .... any help overcoming this barrier will be greatly appreciated.

Here is a sample of my data....

WSOP_Test16.xlsm
	A	B	C	D	E	F
1
2			LDSP
3			Vacancy
4	44743	44743	E1	0.291667	0.625	8
5	44744	44744
6	44745	44745
7	44746	44746	E1	0.291667	0.625	8
8	44747	44747	E1	0.291667	0.625	8
9	44748	44748	E1	0.291667	0.625	8
10	44749	44749	E1	0.291667	0.625	8
11	44750	44750	E1	0.291667	0.625	8
12	44751	44751
13	44752	44752
14	44753	44753	E1	0.291667	0.625	8
15	44754	44754	E1	0.291667	0.625	8
16	44755	44755	E1	0.291667	0.625	8
17	44756	44756	E1	0.291667	0.625	8
18	44757	44757	E1	0.291667	0.625	8
19	44758	44758
20	44759	44759
21	44760	44760	E1	0.291667	0.625	8
22	44761	44761	E1	0.291667	0.625	8
23	44762	44762	E1	0.291667	0.625	8
24	44763	44763	E1	0.291667	0.625	8
25	44764	44764	E1	0.291667	0.625	8
26	44765	44765
27	44766	44766
28	44767	44767	E1	0.291667	0.625	8
29	44768	44768	E1	0.291667	0.625	8
30	44769	44769	E1	0.291667	0.625	8
31	44770	44770	E1	0.291667	0.625	8
32	44771	44771	E1	0.291667	0.625	8
33	44772	44772
34	44773	44773
Sheet3

Here is wsHold.Range("B3")

WSOP_Test16.xlsm
	A	B	C
1		Date	Serial
2	On Open	2022-07-12	44754
3	On Change	2022-07-19	44761
Hold

Cell Formulas
Range	Formula
C2:C3	C2	=B2

My code ...

Sub StaffOnChange()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim staffPath As String
    Dim staffFile
    Dim staffOpn As String
    Dim stfRow As Long
    Dim nWSName As String
    Dim wsSS As Worksheet
    Dim changeDate As Date
    
    Set wsHold = Application.ThisWorkbook.Worksheets("Hold")
    
    staffPath = "D:\WSOP 2020\SupportData\"
    staffFile = wsHold.Range("N1")
    staffOpn = staffPath & staffFile
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & staffOpn & ".xlsm;" & _
        "Extended Properties='Excel 12.0 Macro;HDR=YES';"
    cn.Open

    With rs
        .ActiveConnection = cn
        .Source = "SELECT * from [MASTER$]"
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open
    End With

    Worksheets.Add
    nWSName = ActiveSheet.Name
    Set wsSS = Worksheets(nWSName)
    Range("A2").CopyFromRecordset rs
    rs.Close
    cn.Close
    Stop
    changeDate = wsHold.Range("C3").Value

    With wsSS
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("A4").AutoFilter Field:=1, Criteria1:=changeDate
        .AutoFilterMode = False
    End With
     
    Application.DisplayAlerts = False
    wsSS.Delete
    Application.DisplayAlerts = True
    
End Sub

Still not revealing any data. All rows of the data are excluded from the filter and the result is an empty worksheet, except for the header values.

 

[Alex Blakenburg]

Can you try replacing this

    With wsSS
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("A4").AutoFilter Field:=1, Criteria1:=changeDate
        .AutoFilterMode = False
    End With

With this:

    With wsSS
        If .FilterMode Then .ShowAllData
        .Range("A3").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Format(changeDate, "yyyy-mm-dd"))
        .AutoFilterMode = False
    End With

 

[Ark68]

Hi Alex, I'm afraid that didn't work either. I've attached the workbook with data to see if anyone wants to try it on their end
Sheet1 holds the data that was imported from the closed workbook via the ABODB procedure and for which the filter is applied. I've commented out the portion that sets up the connection and transfers the data over.

I tested in the immediate window with the match function, and in trial and error was able to get a match when matching serials, but unable to without error to get matches using a formatted date (22-07-21 or "2022-07-21") even if the data in the column of data was formatted as such. So, if I can't get the filter to work, I know I can use the match function using the serial number to get to me to a useful position.

 

[Alex Blakenburg]

Match is much more forgiving in terms of Date format. Filter and Find are much less forgiving.
The spreadsheet you have attached has the date column formatted as General. What date format are you using in the real spreadsheet ?

 

[John_w]

Change:

.Range("A4").AutoFilter Field:=1, Criteria1:=changeDate

To:

 .Range("A4").AutoFilter Field:=1, Criteria1:=CLng(changeDate)

 

[Alex Blakenburg]

You could give this a try:
Note: I have changed the changeDate dim from a Date to a String

    Dim changeDate As String
    changeDate = Application.Text(wsHold.Range("C3").Value, wsSS.Range("A4").NumberFormatLocal)

    With wsSS
        If .FilterMode Then .ShowAllData
        .Range("A3").AutoFilter Field:=1, Criteria1:=changeDate
        .AutoFilterMode = False
    End With

 

[Ark68]

Hi John_w and Alex ... thank you both for your solutions. Both worked and are giving me the results I seek. I'm not quite sure why, other than to accept what Alex stated "Match is much more forgiving in terms of Date format. Filter and Find are much less forgiving." The column being "General" I guess is how it got ported over from the recordsource as the data I provided is raw and unaltered. The ADODB recordset when carried over did not copy any formatting of the original data, which is fine as it's only reference data, and my vba aplication can format the data as it needs it. The source is only temporary to extract values.