Detecting if Dayligh Saving Time within Access

Posted on 2009-04-30
Last Modified: 2012-05-06
I have an Access database that creates iCalendar/vCalendar files.  The files are created correctly, but if Daylight Saving Time is in operation it adds an hour to the time when opened in Outlook.  

This was working fine, until the clocks went forward a few weeks ago.

Any ideas?
Question by:Andy Brown
    LVL 11

    Accepted Solution

    Try this:
    Public Function IsDST(ByVal dDate As Date) As Boolean
    '* Copyright © 2005  Camarillo Technology Associates (
    '* Updated for new DST guidelines 4/30/2009 by Sean Strickland
      Dim dStartDST As Date
      Dim dEndDST As Date
      Dim DaysTilSun As Long
      If Not IsDate(dDate) Then Exit Function
      If Year(dDate) = 1899 Then Exit Function
      '* 1899 means it's probably a time-only value, so just bail
      '* Figure out the DST start/end for the supplied year
      '  (Assume DST rules as of now - 4/30/2009)
      '* Figure out how many days after Mar 8, [Supplied Year] that Sunday occurs
      '   Then set when DST starts
      DaysTilSun = 8 - Weekday("March 8, " & CStr(Year(dDate)), vbSunday)
      If DaysTilSun = 7 Then DaysTilSun = 0
      dStartDST = DateAdd("d", DaysTilSun, "March 8, " & CStr(Year(dDate)))
      '* Figure out how many days before Nov 1, [Supplied Year] that Sunday occurs
      '   Then set when DST starts
      DaysTilSun = Weekday("November 1, " & CStr(Year(dDate)), vbSunday) - 1
      dEndDST = DateAdd("d", -DaysTilSun, "November 1, " & CStr(Year(dDate)))
      '* Note that ANYtime on the Sunday that DST starts is considered DST and
      '   ANYtime on the day it ends is considered ST
      IsDST = DateDiff("d", dDate, dStartDST) <= 0 And _
       DateDiff("d", dDate, dEndDST) > 0
    End Function

    Open in new window


    Author Closing Comment

    by:Andy Brown
    Perfect - thank you - very much appreciated.

    Just out of curiosity, if I had a client in another part of the World (we are in the UK), would I to run any other checks?

    LVL 11

    Expert Comment

    Yeah.  The only bad thing about this function is that it has to use static start/end dates for DST.  I'm honestly not sure how you would set it up for clients that span multiple regions with different DST start/end dates.  I would assume that you would need a table somewhere that defined where each client was located and have logic built into the function to support that.

    Author Comment

    by:Andy Brown
    What a pain.  Thanks for all of your help on this.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now