"Argument #1 of 'DatePart' is out of range"

[Blopez70]

Hi,

I created a formula that identifies holidays not using hardcoded dates in my detail lines. It works fine for dates this year, but when a date parameter from last year is entered, I get the out of range error message. I'm not sure what I haven't accounted for. All data, including dates, are pulled directly from a table.

Dim d1,LastDayOfMonth,ThirdMonday,FirstDayOfMonth,FirstMonday,LastMonday,SecondMonday, FourthThursday

d1 = cdate({tblOpsReportFinal.Date})
LastDayOfMonth = DateSerial(Year(d1), Month(d1) + 1, 1 - 1)
FirstDayOfMonth = DateSerial(Year(d1), Month(d1), 1)
ThirdMonday = FirstDayOfMonth + (3 * 7 - (WeekDay(FirstDayOfMonth, crMonday) - 1))
FirstMonday = FirstDayOfMonth + (1 * 7 - (WeekDay(FirstDayOfMonth, crMonday) - 1))
SecondMonday = FirstDayOfMonth + (2 * 7 - (WeekDay(FirstDayOfMonth, crMonday) - 1))
FourthThursday = FirstDayOfMonth + (4 * 7 - (WeekDay(FirstDayOfMonth, crThursday) - 1))

if  (DatePart("m",d1) = 1 and datepart("dd",d1) = 01) or (DatePart("m",d1) = 1 and d1 = ThirdMonday) or (DatePart("m",d1) = 2 and d1 = ThirdMonday) or _
    (DatePart("m",d1) = 5 and d1 = ThirdMonday) or (DatePart("m",d1) = 7 and datepart("dd",d1) = 04) or _
    (DatePart("m",d1) = 9 and d1 = FirstMonday) or (DatePart("m",d1) = 10 and d1 = SecondMonday) or _
    (DatePart("m",d1) = 11 and datepart("dd",d1) = 11) or (DatePart("m",d1) = 11 and d1 = FourthThursday) or _ 
    (DatePart(&quot;m&quot;,d1) = 12 and datepart(&quot;dd&quot;,d1) = 25) and (weekday(d1,crsaturday)<>1 or weekday(d1,crsunday)<>1) then
    
formula = cryellow

elseif (weekday(d1,crsaturday)=1 or weekday(d1,crsunday)=1) then
    formula=crsilver
else
    formula=crwhite

end if

Sorry if I put more than needed here. I didn't know what would be helpful. Thanks for any help you can give.

 

[synapsevampire]

I'd guess that the date has a null (or blank) in it, or is improperly formatted.

Since you're using a cdate function, it isn't a date type field, so I'd do some discovery there.

This assumes that you know that the error is being generated in this formula.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Blopez70]

synapsevampire,

Thanks for your reply. As it turns out, I discovered the snafu (I guess). As usual, the problem was in the "little" details. I placed "dd" in DatePart instead of "d", which seemed to cause the trouble. Why there was no problem with 2003 dates, I really don't know. Anyway, thanks for your help.

-B