selected Calendar date function to test for Holiday

[Neicey23]

I've got a Application that I am trying to test the date selected on an
ACCESS calendar to display if it is a Holiday (which I have stored on a
table). I'm new at this, and believe I need to convert the date but don't
know how.
Here's what I have so far:
Dim dt As String
dt = Me.selecteddate.Value

'------2008 Holiday's ----
If Me.selecteddate = #1/1/2008# Or #2/18/2008# Or #5/26/2008# Or
#7/4/2008# Or #9/1/2008# Or #11/11/2008# _
Or #11/27/2008# Or #11/28/2008# Or #12/24/2008# Or
#12/25/2008# Then MsgBox "Date Requested Is a Holiday! ", vbOKOnly
End If

 

[Ken Sheridan]

When you use the Or operator you need to include both operands each time e.g.

If Me.selecteddate = #1/1/2008# Or Me.selecteddate = #2/18/2008# <and so on>

but hard coding the holiday dates is not a good way of doing it,
particularly as you apparently have them in a table already. You can look
the date up in the table and if it doesn't return Null then it’s a holiday.
Say you table is called Holidays and has a column HolidayDate then the code
would be along these lines:

Const ConMESSAGE = "Date Requested Is a Holiday!"
Dim varDate As Variant
Dim strCriteria As String

strCriteria = "HolidayDate = #" & Format(Me.SelectedDate, "mm/dd/yyyy") & "#"
varDate = DLookup("HolidayDate", "Holidays", strCriteria)

If Not IsNull(varDate) Then
MsgBox ConMESSAGE, vbExclamation, "Warning"
End If

Formatting the date is because date literals must be in US short date format
or an otherwise internationally unambiguous format. If your system is using
US date formats already then this doesn't matter of course, but on this side
of the pond its essential as we normally use a dd/mm/yyyy short date format,
so 4 July would otherwise become 7 April! Doing it regardless of the system
date settings is a good idea anyway as it internationalizes the application.

Ken Sheridan
Stafford, England