Simple function to Check if date is DST (Daylight Saving Time)

[Danick]

I would do it more like...

Private Sub DSTEND_AfterUpdate()
    SetDST
End Sub

Private Sub DSSTART_AfterUpdate()
    SetDST
End Sub

Private Sub Form_Current()
    SetDST
End Sub
    
Private Sub SetDST()
    If IsDate(Me.dststart) And IsDate(Me.dstend) Then
        Me.chkdst = IsDst(Me.dststart, Me.dstend)
    Else
        Me.chkdst = False
    End If
End Sub

Private Function IsDst(d1 As Date, d2 As Date) As Boolean
    IsDst = d1 < Now And Now < d2
End Function

hth
Mark

Thanks Mark.
That works as well and seems to be a better way of doing it.
The only thing I don't like is that the record goes into edit mode as soon as the form opens. I think this is because of the on current event and one of the reasons I didn't use that trigger. Both seem to work though, so I think I'll keep my version unless something goes wrong when I implement it tomorrow (or if you have any other reasons for not doing it my way)...

Anyway, thanks again for taking the time to work on this.
Very much appreciated.

 

[Danick]

One more thing before I implement this, I forgot that I'll need to have an update query or some way to update all the ChkDST checkboxes in the table. Otherwise, the user will have to update each record one at a time.

This is what I've got so far using the query that is in the attached database above.

UPDATE tblCountry INNER JOIN Query1 ON tblCountry.CountryID=Query1.CountryID SET tblCountry.ChkDST = Query1.Active;

This seems to work but there are many warnings.
Any ideas as to a better way of doing that using the SetDST method?

Also I took another look at the SetDST and found it works just as well by taking out the VBA from the Form's On Current event. No more going into edit mode when you click on a record.

 

[MarkK]

It doesn't make any sense to store the result of a calculation made using the value Now(). That is something you need to calculate on-demand.
Mark

 

[Danick]

It doesn't make any sense to store the result of a calculation made using the value Now(). That is something you need to calculate on-demand.
Mark

You are absolutely right and one day I'll have to go back and re-structure the table so that the calculation is done on-demand just on the forms.

The reason it I'm doing it this way for now is because the DST is a field in a table. And it will add or remove one hour for DST depending on a Checkbox.

So I thought of it happening automatically by adding two other fields that will set the check box and table field automatically by using your SetDST VBA. It's working very well. But I think one day, I'll go back and work on getting rid of the DST field and allow the calculation on demand. For now, I'll just use an update query to set the field in the table the old way.

Thanks for you help

 

[pstraton]

Hi,

What would it be for the UK.

Last sunday in march & last Sunday in October.

many thanks

Dace

The attached file contains several routines that may be helpful. (It was developed under Excel but is all standard VBA so it should execute under Access as well):

CheckDST: Full-featured version. Returns True if Daylight Savings Time (or British Summer Time) applies to a specified date (in an optionally specified locale); otherwise returns False. Handles all US DST (and UK BST) system variations back to 1966, including President Nixon's "Emergency Daylight Saving Time Energy Conservation Act" of 1973 and Harold Wilson's "British Standard Time" experiment, October 27, 1968 to October 31, 1971.

CheckDST_UK1972: Simplified version. Returns True if the UK British Summer Time applies to a specified date, based on the BST system as it has been defined since 1972; otherwise returns False.

CheckDST_US2007: Simplified version. Returns True if the US Federal Daylight Savings Time applies to a specified date, based on the DST system established in 2007; otherwise returns False.

IsDST: Returns True if Daylight Savings Time is currently in effect (in the optionally specified locale); otherwise returns False.

NthDayOfWeekDate: Returns the Date of a specified Nth instance of a specified day-of-week in a specified month.

 

[The_Doc_Man]

Get Timezone Information in VBA (Excel)

I would like to determine a time offset to GMT/UTC (including daylight saving time) for different countries at a specific date in VBA. Any ideas? EDIT (from self-answer): Thank you 0xA3. I quickly

stackoverflow.com

Shows a VBA routine to detect whether you have DST or not.

It creates a call to a system routine GetTimeZoneInformation.

 

[pstraton]

Shows a VBA routine to detect whether you have DST or not.

It creates a call to a system routine GetTimeZoneInformation.

Yes, if all one needs is to determine the current-date DST status in one’s local time zone then use of GetTimeZoneInformation via Pearson’s DaylightTime function would make lots of sense. But, ironically, the need for the more sophisticated functionally of my routine set (above) was driven by my use of Pearson’s GetFileDateTime and SetFileDateTime functions, which use the Windows API functions LocalFileTimeToFileTime and FileTimeToSystemTime. Both of those functions apply the current-date state of DST in the local time zone regardless of the date passed to them, and Pearson’s code does not compensate for a file date that might be in the opposite DST status from the current date (i.e. six months out of phase). So I’ve had to enhance Pearson’s functions to check for that case and make the necessary adjustment.

In Pearson’s SetFileDateTime function, after its file-exists check, I’ve inserted this code:

If IsDST Then
    'Is currently DST, so add an hour if the file date/time to be written includes a standard-time date.
  
    If Not CheckDST(FileDateTime) Then FileDateTime = FileDateTime + TimeSerial(1, 0, 0)
Else
    'Is not currently DST, so subtract an hour if the file date/time to be written includes a DST date.
  
    If CheckDST(FileDateTime) Then FileDateTime = FileDateTime - TimeSerial(1, 0, 0)
End If

In Pearson’s GetFileDateTime function, before it closes the file handle and sets the function’s return value, I’ve inserted this code:

If IsDST Then
    'Is currently DST, so subtract an hour if the file's date/time includes a standard-time date.
  
    If Not CheckDST(ResultTime) Then ResultTime = ResultTime - TimeSerial(1, 0, 0)
Else
    'Is not currently DST, so add an hour if the file's date/time includes a DST date.
  
    If CheckDST(ResultTime) Then ResultTime = ResultTime + TimeSerial(1, 0, 0)
End If

All of that is a bit off-topic for this thread but it does illustrate well the need for my functions and their use.

As for the design of my IsDST function and why it isn’t just a wrapper for GetTimeZoneInformation, it seemed like a good trade-off of simplicity for theoretically slower execution: it’s simple because all I have to do is pass the current date to my also-needed function CheckDST and it requires no additional Windows API declaration, but it is implemented in VBA code so is undoubtedly slower than the underlying, compiled GetTimeZoneInformation code. But it’s hard to imagine a scenario in which such a speed trade-off really matters. I use it for the occasional file-date/time capture and/or modification, nothing iterative and nothing computationally intensive.

 

[The_Doc_Man]

OK, I read your explanation. I don't understand the need. You are looking to set a file time - but either updating or creating a file through the standard mechanisms will use the current time in the current time zone and automatically set the correct property of the file. To want to set otherwise suggests something unusual is going on. I don't deny the possibility but in 28 1/2 years with the U.S. Navy's data centers, I've only seen one case where there was a need to set a time to other than the current time of the system as of the create/modify operation. And in that case, the problem was that the transfer method didn't preserve the original file date/time but the first record of the actual file DID. However, with modern networking stacks, every protocol I know for full file transfer preserves the correct time now. So I'm a bit curious if nothing else. Why do you think you need to diddle with the time on the file?

 

[pstraton]

OK, I read your explanation. I don't understand the need....So I'm a bit curious if nothing else. Why do you think you need to diddle with the time on the file?

There are plenty of examples of a need to modify file date/times. Here are a few of mine:

My first need for Pearson’s file-date/time functions was a project for which the organization needed a system to suck data out of a massive pile of complex Word-document forms and organize it in corresponding Excel workbooks, for analysis and reporting by relatively non-technical users. Part of the requirement was that the users needed to be able to sort the resulting Excel files, in File Explorer, by content type (file name) and by analysis-workbook date (Created date) but also by the date of a content field in the original Word doc form. So I used Pearson’s SetFileDateTime function to set the resulting Excel file’s Last-Modified date to the form-content date value, and set the file’s Read-only attribute in order to prevent subsequent modifications to it. Then the user could sort by topic (file name), report-file Created date, or form-content date (report-file Modified date) as needed. It was a crude but easy-to-use, simple reporting system that got a lot of mileage. That design did have the odd effect of the files having Modified dates older than their Created dates, but everyone understood the context and functionality, so it wasn’t a problem.

Eventually, I realized I could use Pearson’s functions to work around an annoying “feature” in the Windows file system: unlike files, when a folder is copied to a different drive (for backup/archive purposes), Windows sets the folder’s Last-Modified date and Last-Accessed date to be the same as the its Created date, instead of copying the original folder’s dates. One of my programmer’s survival tools has been to regularly make archival backup copies of the source-files for whatever system I’m developing, in case regression testing reveals yet another bone-headed, naive design choice, and I have to back up, possibly several steps, and take a different tack on the solution. But I need to organize the archive folders (and their contained files) by the last-modified date of the original folder (or file). So I modified Pearson’s functions to work with folders, in addition to files, and built a little tool that allows me to fix that annoying folder-date “feature” of Windows.

And then I realized I could upgrade that tool to work around a design-flaw in the programmer himself: he's a fallible human being. One of the ways that manifests is that, when digging deep into my archived designs, to determine where I went wrong or to just get ideas about how to maybe adapt those designs to new purposes, I sometimes get distracted by the urgencies of life, or am just tired late in the day, and lose track of which file I have open in which Window and then accidentally re-save an old archive file and thereby set its last-modified date/time to the present time, screwing up my sort-by-date/time organization of those files. The current version of my tool allows me to reset such file's modified date back to what it was originally. For that use-case, accurate time stamp (not just date) became critical so I needed to further upgrade Pearson’s design to properly handle daylight-savings time. Hence the above utilities.