RETRiEVE ONLY TODAYS APPOINTMENTS

[CHEECO]

Hello everyone



What I am trying to do is a an appointment reminder retrieve daily appointment, not all appointments for the year. The below is the code I am using. As it is it retrieves all appointment on the yearly calendar. Thank-you Ishould mention that I am using Access 2016.

Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[ApptID]", "[tblAppointments]", "[ApptStart] <=now() AND [Complete] =0")
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
            Exit Sub
                Else
                    If MsgBox("There are " & intStore & " uncompleted jobs" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
                    DoCmd.Minimize
                    DoCmd.OpenForm "frmReminders", acNormal
                Else
            Exit Sub
        End If
    End If
End Sub

[CJ_London]

which line of your code is retrieving all appointments for the year?

do you mean counting?

[CHEECO]

I believe it is this line. The [ApptStart] is the date of the appointment in the table. The next part of the code "<=now()" I believe is what calls the date to the appointment reminder. I changed the "Now" to "Date" and it did not work.

intStore = DCount("[ApptID]", "[tblAppointments]", "[ApptStart] <=now() AND [Complete] =0")

[John_G]

I think the problem is here:

DoCmd.OpenForm "frmReminders", acNormal

What is the record source for the form? If you only want to see "today's" appointments, then you will have to filter the displayed records on the Docmd.openform statement, or base the form on a query which shows only the records for the current date.

[CJ_London]

if you want todays appointments

"datepart('yyyy',[ApptStart])=date()