Date converting problem in function.

[m001]

Date converting problem in function. I think this might be an interesting one.

Here we go,

I used a function "OfficeClosed" (from the Microsoft site) to know if a specific date falls on a Saturday, Sunday or Holiday.

Determining whether it's a Saturday or Sunday happens through the built-in "Weekday function", to find holidays the function does a DLookup in my holiday table.

The good thing is, it works! But...

My regional settings are put to European date, I'm sure you know but just to be sure I type the day first, then the month and then the year.

'6/11/03' reads 6 november 2003 and not 11 june 03.

So the dates in my holiday table are typed and read in this way.

The function formats my Integer (TheDate) as "dd/mm/yyyy".

After typing the function I tried it in the immediate pane and what happens:

The 'Weekday function' works fine, but reads the date the American way.
The dates of my table are also seen the American way (even if my regional date settings are ok), so this goes wrong.

I tried to be smart and change the date format of TheDate in: "mm/dd/yyyy".
Guess what happens: the 'weekday' function gives me the wrong result but now the holidays are ok .

I think it's so strange that the weekday function doesn't seem to take the regional settings into account and I thought that the format "dd/mm/yyyy" was the European way, but strangely enough it seems to be the other way around.

So what am I looking for?

A way to make the 'weekday()' function read my dates the European way or any other solution.

Here's the OfficeClosed() function, I'm talking about:
-----------
Function OfficeClosed(TheDate) As Integer

OfficeClosed = False
TheDate = Format(TheDate, "dd/mm/yyyy")
' Test for Saturday or Sunday.
If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
OfficeClosed = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
OfficeClosed = True
End If

End Function
-----------------

Second part of my question

As holidays come back every year at the same date I think it's a bit crazy to fill my table yearly with the holiday dates.

What I'd like to do is letting the user put in a day (number field) and a month (number field).
Then perform a 'query?' to build a date, so:

day: 3
month: 10

would be changed into 3/10/03.

I thought this might work with following calculation in my query:

Date: [day]&"/"&[month]&"/"&year(Today)

If I find a way to make this work I could run this query as an update query and fill in a field [thisyear] with the actual date the holiday takes place this year.
This will avoid the input of a lot of unnecessary data every year.

But...

The query doesn't accept the 'Today', what should I use to make the actual year appear?

Third part

Thank you for reading and eventually answering my questions.
Your help means a lot to me.

Michèle

 

[Mile-O]

Be more explicit:

Public Function OfficeClosed(ByVal TheDate As Date) As Boolean

    ' Test for Saturday or Sunday. 
    If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Or _
        Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _ 
& TheDate & "#")) Then 
        OfficeClosed = True 
    End If

End Function

That's just your code tidied with one bit removed. I suspect the Format() function was ruining your function as it returns a string. Your variable begins is a Variant that is initially a Date and then becomes a String. Shouldn't be necessary. Try what's above.

As for getting the current Year: Year(Date()) is all you need.

Also, if you're doing UK Public Holidays etc then you may want to search on my name - I've done loads of calculation examples - between dates, to dates, etc. that take holidays into account.