Access selecting specific date on a calendar

I think the issue may actually be coming from how txtSelectDate is being populated or interpreted rather than the Format() function itself. If Me.txtSelectDate is not holding a full valid date value internally, Access can sometimes substitute the current day when rebuilding the date with CDate().

You probably do not need to split the date into separate day/month/year variables at all. Try simplifying it like this instead:

Private Sub lblTue3_Click() Dim dSelectDate As Date dSelectDate = Me.txtSelectDate.Value DoCmd.OpenForm "frmThatContainsMyData", _ acNormal, , _ "[DateOnFormUsedForFilter]=#" & Format(dSelectDate, "yyyy-mm-dd") & "#", _ , acDialog End Sub

Using the ISO format (yyyy-mm-dd) is usually much safer in Access filters because it avoids regional date interpretation issues.

Also double check that txtSelectDate really contains the exact clicked date and not only the month/year display value from the calendar form.

I recently ran into a somewhat similar date parsing issue while testing some VBA utilities and date handling logic on one of my own small tool projects:
https://peptixcalc.com

Access date handling can be surprisingly tricky sometimes.