Checking Date for Holiday/Weekend and getting 1/1/1900 or 12/31/1899

[sgtSortor]

I reserve, check-out and check-in equipment that is loaned out to our employees (projectors, microphones, laptops, etc.)

When putting in a date the equipment needs to be picked up, I run it through the following code to ensure we don't reserve for weekends or holidays.

Unfortunately, when testing in the immediate window, it keeps telling me that my date is a saturday and changes it to 1/1/1900 . I'm using 7/4/2014 as my test date.

Function HolidayWeekend(vDate) As Integer
 '1/1/1900 is the way that Access interprets the numeric value 0 as a date.
   HolidayWeekend = False
     vDate = Format(vDate, "mm/dd/yyyy")
   ' Test for Saturday or Sunday.
   If Weekday(vDate) = 1 Then
        vDate = DateAdd("d", 2, vDate
        MsgBox "was sunday " & vDate
       HolidayWeekend = True
       Exit Function
    ElseIf Weekday(vDate) = 7 Then
        vDate = DateAdd("d", 2, vDate)
        MsgBox "was saturday " & vDate
       HolidayWeekend = True
       Exit Function
   ' Test for Holiday.
   ElseIf DLookup("[HolidayDate]", "[ltblHolidays]", "[HolidayDate]= " & vDate) Then
      MsgBox "Holiday is " '& DLookup("Holidaydate", "ltblHolidays", "[HolidayDate]=" & vDate)
       HolidayWeekend = True
       'Exit Function
   End If
End Function

Thanks, Any help will be greatly appreciated. :banghead:

 

[namliam]

Function HolidayWeekend(vDate AS DATE) As Integer

Dont be lazy.... same goes for your date, try
HolidayWeekend #07/04/2014#
Note this is in MM/DD/YYYY

Same goes for your Lookups, doing 2 Lookups btw is a NO NO considerable drain on performance , but dates need ##
DLookup("[HolidayDate]", "[ltblHolidays]", "[HolidayDate]= #" & Format(vDate, "MM/DD/YYYY" & "#")



Remove this line:
vDate = Format(vDate, "mm/dd/yyyy")
It is utter nonsense (not meant to be insulting)

 

[pr2-eugin]

Try this instead !

Function HolidayWeekend(vDate As Date) As Boolean
[COLOR=Green]    '1/1/1900 is the way that Access interprets the numeric value 0 as a date.
    ' Test for Saturday or Sunday.[/COLOR]
    Select Case Weekday(vDate)
        Case vbSaturday, vbSunday
            MsgBox vDate & " - Falls on a " & WeekdayName(Weekday(vDate), False, vbSunday) & ". Please enter a weekday only.", vbInformation
            HolidayWeekend = True
        Case Else
            If DCount("*", "ltblHolidays", "HolidayDate = " & Format(vDate, "\#mm\/dd\/yyyy\#")) <> 0 Then
                MsgBox vDate & " - Seems to be a bank holiday. Please enter a working day only.", vbInformation
                HolidayWeekend = True
            End If
    End Select
End Function

EDIT: Arrrrr ! namliam