Access selecting specific date on a calendar

Make sure the textbox holds text that can be converted to a date:

Private Sub lblTue3_Click()

    Dim dSelectDate As Date

    Debug.Print "Text", Me!txtSelectDate.Value
    dSelectDate = DateValue(Me!txtSelectDate.Value)
    Debug.Print "Date", dSelectDate

I suspect my problem has to do with the code I used to build the calendar. I watched some YouTube videos and based my code off of them. Some additional information:

The calendar has 42 blocks, all labels. Each block has a small box in the top right corner that will display the day number; these are command buttons (but the buttons don’t do anything). There is also a combo box where the user can choose the month (cboMonth) and a text box where the user can put in the year (cboYear).

The term ‘Capsule’ refers to programs teachers can check out from us, like a library system.

DateShippedOut is when the Capsule leaves our office. DateReturn is when the Capsule should be back at our office. The calendar is set up to show all the days a capsule is out from DateShippedOut to DateReturn. I have a query that lists all of the dates a Capsule is out called ‘NewDate’. rmClassDataEntry is the form I want to appear in a pop-up that shows more information about all the capsules checked out on whatever date the user clicks on.

Most of the code works. All of the Capsules shows up on the calendar just fine. The only issue I’m having is what I mentioned above: 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. As peptixcalc mentioned, I think txtSelectDate isn’t storing the full date, just the month and year.

Here is my code, not including the modules (which I can share if needed). Images of the calendar are below.

Private Sub DisplayCalendar()
    Dim ctl As Control
    Dim FirstDay As String
    Dim iNumberofDay As Integer
    Dim iDay As Integer

'iDay will display on the buttons we created
    
    FirstDay = Format(DateSerial(Year(Me.txtSelectDate), _
Month(Me.txtSelectDate), 1), "ddd")
    iNumberofDay = Format(DateSerial(Year(Me.txtSelectDate), _
Month(Me.txtSelectDate) + 1, 0), "dd")
    iDay = 1
        
    Call ClearCalendar

    For Each ctl In Controls
        If iDay <= iNumberofDay Then
            If TypeName(ctl) = "commandbutton" And IsValidDay _
(Mid(ctl.Name, 4, 3)) Then
                If FirstDay = Mid(ctl.Name, 4, 3) Then
                    Call CapsuleTitleDbAccess.DisplayCapsuleInfo _
(DateSerial(Year(Me.txtSelectDate), Month(Me.txtSelectDate), _
iDay), Me.Name, "lbl" &  Mid(ctl.Name, 4, 4))
                    ctl.Caption = iDay
                    iDay = iDay + 1
                    FirstDay = ""
                ElseIf FirstDay = "" Then
                    Call CapsuleTitleDbAccess.DisplayCapsuleInfo(DateSerial _
(Year(Me.txtSelectDate), Month(Me.txtSelectDate), iDay), Me.Name, _
"lbl" & Mid(ctl.Name, 4, 4))
                    ctl.Caption = iDay
                    iDay = iDay + 1
                End If
            End If
        End If
    Next
End Sub
______________________________________________________________________________

Private Sub OptView_Click()
Call DisplayMonth
    Me.frmClassDataEntry.RefreshForm
    
End Sub
______________________________________________________________________________

Private Sub ClearCalendar()

    Dim ctl As Control
    
    For Each ctl In Controls
        
        If TypeName(ctl) = "commandbutton" And IsValidDay _
(Mid(ctl.Name, 4, 3)) Then
                ctl.Caption = ""
        End If
        
         If TypeName(ctl) = "label" And IsValidDay(Mid(ctl.Name, 4, 3)) Then
            ctl.Caption = ""
        End If
    Next
End Sub
______________________________________________________________________________

Private Function IsValidDay(sControlName As String) As Boolean
    If sControlName = "Sun" Or sControlName = "Mon" Or sControlName = "Tue" Or _
        sControlName = "Wed" Or sControlName = "Thu" Or sControlName = "Fri" _
            Or sControlName = "Sat" Then IsValidDay = True
           
End Function
______________________________________________________________________________

Private Sub cboMonth_Click()
    Me.txtSelectDate = CDate(Me.cboYear & "/" & Me.cboMonth & "/" &  _
Day(Me.txtSelectDate))

         Call DisplayMonthName
End Sub
______________________________________________________________________________

Private Sub cboYear_AfterUpdate()
   Me.txtSelectDate = CDate(Me.cboYear & "/" & Me.cboMonth & "/" &  _
Day(Me.txtSelectDate))
  
            Call DisplayMonthName
End Sub
______________________________________________________________________________

Private Sub cmdNextMonth_Click()
    
    Me.txtSelectDate = Me.txtNextMonth
    Call DisplayMonthName
    
End Sub
______________________________________________________________________________

Private Sub cmdPreviousMonth_Click()
    
    Me.txtSelectDate = Me.txtPreviousMonth
    Call DisplayMonthName
    
End Sub
______________________________________________________________________________

Private Sub Form_Load()

    Me.txtSelectDate = Date
    
    Call DisplayMonthName

End Sub
______________________________________________________________________________

Private Sub DisplayCapsules()
‘not sure this is necessary
    Dim db As Database
    Dim rs As Recordset
    Dim str As String
    Dim DateShipOut As Date
    Dim DateReturn As Date
    Dim displayCapsuleTitle As String
    
End Sub
______________________________________________________________________________

Private Sub DisplayMonthName()
‘for buttons to change the month
    Dim PreviousMonth As Date
    Dim NextMonth As Date
    
    Me.cboYear = Year(Me.txtSelectDate)
    Me.cboMonth = Month(Me.txtSelectDate)
    
    PreviousMonth = DateAdd("m", -1, Me.txtSelectDate)
    NextMonth = DateAdd("m", 1, Me.txtSelectDate)
    
    Me.txtPreviousMonth = PreviousMonth
    Me.txtNextMonth = NextMonth
        
    Me.cmdNextMonth.Caption = modCalendar.MonthNameByDate _
(NextMonth)
    Me.cmdPreviousMonth.Caption = modCalendar.MonthNameByDate _
(PreviousMonth)

    Call DisplayCalendar
        
End Sub
______________________________________________________________________________
'just to show one example

Private Sub lblTue3_Click()
Dim dSelectDate As Date
dSelectDate = Me.txtSelectDate.Value

DoCmd.OpenForm "frmClassDataEntry", acNormal, , "[NewDate]=#" &  _
Format(dSelectDate, "yyyy-mm-dd") & "#", , acDialog

End Sub

This is what the calendar looks like:

Any help is greatly appreciated. If you need additional information, please let me know.

Thank you so much!

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] = #" & Format(SelectDate, "yyyy\/mm\/dd") & "#", , acDialog

End Sub

' or even:

Private Sub lblTue3_Click()

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

End Sub

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.

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.