Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Red face Unanswered: Formating date in function (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

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Date format

    FORGET REGIONAL SETTINGS - IT'S NOTHING TO DO WITH THEM...

    http://support.microsoft.com/default...NoWebContent=1

    Sorry I only have time to help with first part of question
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Nov 2003
    Posts
    17

    Re: Date format

    Originally posted by garethdart
    FORGET REGIONAL SETTINGS - IT'S NOTHING TO DO WITH THEM...

    http://support.microsoft.com/default...NoWebContent=1

    Sorry I only have time to help with first part of question
    Thanks for the link! It's almost unbelievable you have to perform such a workaround.
    Michèle

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Yes

    It is a pain (went through the same experience as you when I first came across this!) but once you get your head round it & use the module, it works fine.

    You can use the module within a query too:

    USDate: MakeUsDate([mydatefield])


    etc.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •