Use query to color labels (1 Viewer)


Registered User.
Local time
Today, 01:41
Feb 28, 2006
I am trying to color labels on a calendar-type form, based on a query.

A table contains various reservations at a property with a number of check in and check out dates. I have a form with 93 labels (3 for each day) and I need to color them based on the check in and check out dates. With the code below, I have managed to find the first check in and check out dates (using dlookup) and color the correct labels, indicating the dates that a guest is staying at a place. That said, that only works for one check in-checkout period as I am using dlookup. How do I go about using a query instead as source so I could have more than one check in and check out?

This is the code for the form:
Private Sub Form_Load()
    Dim Initial As Long
    Dim Length As Long
    Dim Counter As Long
    Dim lngGreen As Long
    Dim Checkin As Date
    Dim Checkout As Date
    Dim lblName As String
    Dim DayOfWeekIn As Long
    Dim DayofWeekOut As Long
    lngGreen = RGB(0, 255, 0)
    Checkin = DLookup("CheckInDate", "qryReservations", "OwnerName='Jean Nicou'")
    Checkout = DLookup("CheckOutDate", "qryReservations", "OwnerName='Jean Nicou'")
    DayOfWeekIn = Weekday(Checkin, 2)   'Find out which day of the week this is with Monday as 1
    DayofWeekOut = Weekday(Checkout, 2) 'Find out which day of the week this is with Monday as 1
    MsgBox WeekdayName(DayOfWeekIn, False, 2)  'Find out the name of the day
    MsgBox WeekdayName(DayofWeekOut, False, 2)  'Find out the name of the day
    Initial = Day(Checkin)      'Find the day of the month that this reservation starts
    Length = Day(Checkout) - Day(Checkin)     'Find the length of the reservation
    'First label to color. Always third label of the first day
    lblName = "lbl" & Initial & "03"
    Me(lblName).BackColor = lngGreen
    Me(lblName).ForeColor = lngGreen
    'Label to color between first and last
    For Position = Initial + 1 To Initial + Length - 1
        For Counter = 1 To 3
            lblName = "lbl" & Position & "0" & Counter
            Me(lblName).BackColor = lngGreen
            Me(lblName).ForeColor = lngGreen
    'Last Label to color
    lblName = "lbl" & Position & "01"
    Me(lblName).BackColor = lngGreen
    Me(lblName).ForeColor = lngGreen
End Sub

This is the SQL for the reservations that I need to somehow have this code use as source.

SELECT tblProperties.PropertyName AS tblProperties_PropertyName, tblProperties.ID AS tblProperties_ID, tblProperties.OwnerName, tblReservations.ID AS tblReservations_ID, tblReservations.GuestName, tblReservations.PropertyName AS tblReservations_PropertyName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationSource, tblReservations.Comments, [CheckOutDate]-[CheckInDate] AS Nights
FROM tblProperties INNER JOIN tblReservations ON tblProperties.[PropertyName] = tblReservations.[PropertyName];

Does anyone know how to do this?



I’m here to help
Staff member
Local time
Yesterday, 23:41
Oct 29, 2018
Hi. Without seeing your form, I just have to ask... Have you tried using Conditional Formatting?


error reading drive A:
Local time
Today, 15:41
May 7, 2009
perhaps move your Code to the Paint Event of the Detail section of the Form?

Users who are viewing this thread

Top Bottom