Issue Using Date Criteria in a Dlookup function in Access VBA

Question

I am checking in the table "weekly data" for a specific date stored in the first row of the table "daily data":

Private Sub Data_Update_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstw As DAO.Recordset

    Set db = Application.CurrentDb
    Set rst = db.OpenRecordset("Data daily", dbOpenDynaset)
    Set rstw = db.OpenRecordset("Data Weekly", dbOpenDynaset)

    With rst
        .MoveFirst

        Dim date_check As Date
        date_check = DLookup("[ID test]", "Data Weekly", "[weekly date] = '" & .Fields("daily date") & "'")

.........

The criteria is causing the problem, cannot find the right syntax.

Answer 1

Use:

date_check = DLookup("[ID test]", "Data Weekly", "[weekly date] = #" & Format(.Fields("daily date").Value, "yyyy\/mm\/dd") & "#")

but date_check must be a Variant as DLookup can return Null.