Excel vba userform trying to filter ado recordset

[Don Quixote]

I have an excel vba userform from which I connect to an adodb (ms access), But I am trying to filter on the date (Datum) field.

Both via sql statement or the recordset.filter method but neither seems to work.

Here is my code:

Private Sub UserForm_Initialize()

    Dim con As Object
    Dim rs As Object
    Dim db As String
    Dim sql As String
   
    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    db = "Q:\Kiel Data\Blending\TEST\Base Oils.accdb"
    'db = "C:\Users\Dennis\Documents\Blending & Filling\Basis Olie Lossing\Base Oils.accdb"
    sql = "SELECT [Datum], [Bestelbon], [Productnaam], [Tank], [Losplaats], [aankomst datum], [aankomst tijd], [Transporteur], [Nummerplaat], [STAAL], [LABO], [TRANSFER], [COMPLETED], [Vertrektijd], [Opmerkingen] FROM [Planning] WHERE [Datum] = #11/12/2024#"

    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & db

    rs.Open sql, con

    txtBestelbon.BackColor = RGB(255, 150, 224)
    txtTransporteur.BackColor = RGB(255, 150, 224)
    txtProduct.BackColor = RGB(255, 150, 224)
    txtLosplaats.BackColor = RGB(255, 150, 224)
    txtTank.BackColor = RGB(255, 150, 224)
    EditButton.BackColor = RGB(76, 255, 0)
    SearchButton.BackColor = RGB(255, 233, 127)
    SelectButton.BackColor = RGB(255, 233, 127)
    'txtAankomstDatum = Format(Date, "dd/mm/yyyy")
    'txtAankomstUur = Format(Time, "hh:mm")

    'rs.Filter = "Datum >= #11/12/2024#"
   
    'ListBox1.ColumnCount = rs.Filter = "Datum = #11/12/2025#"
    ListBox1.ColumnCount = rs.Fields.Count
    ListBox1.ColumnWidths = "60;70;150;40;70;60;60;100;80;60;60;60;60;60;100"
    ListBox1.Column = rs.GetRows
    'ListBox1.ListIndex = 1
    ListBox1.ColumnHeads = True

End Sub

Any suggestions?

thx

 

[DanteAmor]

Try date in yyyy/mm/dd format

WHERE [Datum] = #2024/12/11#"

 

[Don Quixote]

Thx that worked!

But why does it do this? My database displays it as dd/mm/yyyy ??

 

[DanteAmor]

Excel, VBA and other sources work with the standard date in English format "yyyy/mm/dd", even if you visibly have another format.