Hi Richard Hope you can point me in the right direction please. This is where I am at.
Private Sub OpenCalendarForm_Click()
Dim x As Integer
Dim S1 As String, S2 As String, IsHoliday As Integer, GetID As Integer
Dim MySql As String

    CalculateStartDate
    
    DoCmd.OpenForm "MonthlyCalendarF"
For x = 1 To 42
    S1 = "Date" & x
    S2 = "Day" & x
    If Month(Forms!MonthlyCalendarF(S1)) <> Month(Calendar) Then
        Forms!MonthlyCalendarF(S2).BackColor = &HD8D8D8
    Else
        MySql = "'DateTime>=Forms!DatePickerF!StartDate +" & x
        MySql = MySql & " AND DateTime        
        ' tried Dlookup("Holiday","DateQ",mysql) that did not work
        ' so now try to get the ID then use "CalID=" & Getid
        ' that does not work either. Dlookup is not reading the correct values
        
        GetID = DLookup("CalID", "DateQ", MySql)
        IsHoliday = Nz(DLookup("Holiday", "DateQ", "CalID=" & GetID))
        
        'MsgBox GetID & " - " & IsHoliday
        ' result is always 1
        
            If IsHoliday = 1 Then Forms!MonthlyCalendarF(S2).BackColor = vbYellow ' School Holiday
            If IsHoliday = 2 Then Forms!MonthlyCalendarF(S2).BackColor = vbBlue ' Public Holiday
            If IsHoliday = 3 Then Forms!MonthlyCalendarF(S2).BackColor = vbGreen ' Student Free Day
  
    End If
Next x

End Sub