Daylight Saving

[captnk]

Is there a way to find the DATE of the FIRST SUNDAY in OCTOBER in a query.
(Australian daylight saving commences on the first sunday of October in any year so it is a variable)
Sure the PC time calculates ok with the various MS updates,but I need it to apply to existing data in an access table.
Regards

 

[jal]

This is my first attempt at this sort of problem so I hope I got it right.

Let's suppose that WeekDay(#10/07/2008#) = 4 (in other words let's suppose that october 7th falls on the 4th day of the week (I haven't checked).

You want to find the date where Weekday(# the date#) = 1 (Sunday). Well, to find out how far we are away from 1, we need to start from 1 and then go the distance to 4, that is,
1 - 4 (3 days awa in th is sample scenario0
or more generally:
1 - WeekDay(#10/07/2008#)
tells us how many days we are away from the closest Sunday. (I started from Oct 7 rather than Oct 1 to avoid the problem of the closest Sunday being in the past month, that is, in September). Anyway the above formula should gives us the right number of days to add to Oct 7, so now we just have to add that number of days to Oct 7. In our sample scenario above, we would be adding -3 days to Oct 7 which is done like this:
DateAdd("d", -3, #10/07/2008#)

Or more more generally:

DateAdd("d", 1 - WeekDay(#10/07/2008#), #10/07/2008#)

You might want to test this on several different months or years to make sure I got it right.

 

[jal]

You can test it by pasting it into sql View:

SELECT DateAdd("d", 1 - WeekDay(#10/07/2008#), #10/07/2008#)

 

[jal]

Come to think of it, should I have started it on Oct 8th instead of Oct 7th?

October 8th is now beginning to look more appropriate, to avoid falling back into September.

 

[jal]

I still haven't figured this out - Oct 7 or 8?
I'll need to pull out a calendar and do some testing.

 

[jal]

Oh, i guess it must be Oct 7. Because if we use Oct 8th then we could be two sundays deep into the month already. Silly me.

 

[boblarson]

Actually, this will work if you paste it into a new module:

Function Get1stSundayOct(intYear As Integer) As Date
    Dim dteStart As Date
     
    dteStart = DateSerial(intYear, 10, 1)

    If Weekday(dteStart) = 1 Then
        Get1stSundayOct = dteStart
    Else
        Get1stSundayOct = DateAdd("d", 8 - Weekday(dteStart), dteStart)
    End If
End Function

And then you just call it by supplying the year in the query

MyNewField:Get1stSundayOct(Year(Date))

 

[captnk]

Many thanks for the help guys.
I have it working now.
(Now to press on with the next bit)
Regards