Can't populate text box when form opens

I have a form that has a control, txtHoliday.  All I want is when this form opens, I want the box to fill with the holiday if it fall between Date-7 and Date+7.  But it simply keep erroring out.

Private Sub Form_Open(Cancel As Integer)
Dim dteHoliday As Date
dteHoliday = DMax("HolidayDate", "tblHolidays", "HolidayDate > Date() - 7 and HolidayDate < Date + 7")
    Me.cboAssociate = "<All>"
    Me.cboJobDay = "<All>"
    Me.cboProblemGroup = "<All>"
    Me.cboPeriod = "<All>"
    Me.cboAssociate.SetFocus
    Me.txtDate = Date
   Me.txtHoliday = dteHoliday
    
End Sub

Select allOpen in new window