Access selecting specific date on a calendar

Question

I'm pulling my hair out because everything I've found through my research says my VBA code should work, but it doesn't!Here's the story. I have a calendar (form) that shows events going on for each month. What I want is for a person to click on a specific day and have a pop-up form appear showing more details of the events for whatever day they clicked on. I used the below code, which mostly works... When I click on a date, the month and year will be correct, but not the day of the month. The day of the month is always today's day. For example, let's say I click on the day Feb. 2, 2024 and today's date is May 21, 2026. My pop-up form will show the events for Feb. 21, 2024.&nbsp;This is the code I have:Private Sub lblTue3_Click()

Dim txtSelectDate As Date (probably don't need this)

Dim iDayOfMonth As Integer

Dim iMonth As Integer

Dim iYear As Integer

Dim dSelectDate as Date

iDayOfMonth= Format(Me.txtSelectDate.Value, "d")

iMonth = Format(Me.txtSelectDate, "mm")

iYear = Format(Me.txtSelectDate, "yyyy")

dSelectDate = CDate(iMonth &amp; "/" &amp; iDayOfMonth &amp; "/" &amp; iYear)

DoCmd.OpenForm "frmThatContainsMyData", acNormal, , "[DateOnFormUsedForFilter]=#" &amp; dSelectDate &amp; "#", , acDialog&nbsp;Instead of Format, I've tried using the code Day(Me.txtSelectDate), but that didn't work either. It always defaults to the day of the month that's today.Can anyone tell what I'm doing wrong? I've checked the formatting, and everything is set to 'Short Date'... I'm just at a complete loss!Any help is greatly appreciated!

gustavbrock · Answer

You are making it way too complicated. Try:
Private Sub lblTue3_Click()

Dim SelectDate As Date

SelectDate = DateValue(Me!txtSelectDate.Value)

DoCmd.OpenForm "frmThatContainsMyData", acNormal, , "[DateOnFormUsedForFilter] = #" &amp; Format(SelectDate, "yyyy\/mm\/dd") &amp; "#", , acDialog

End Sub

' or even:

Private Sub lblTue3_Click()

DoCmd.OpenForm "frmThatContainsMyData", acNormal, , "[DateOnFormUsedForFilter] = #" &amp; Me!txtSelectDate.Value &amp; "#", , acDialog

End Sub
&nbsp;

peptixcalc · Answer

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:
http://peptide calculator

Access date handling can be surprisingly tricky sometimes.

peptixcalc · Answer