Find first Sunday on or after the 6th of the Month?! (1 Viewer)

[Jibbadiah]

I have some code to do this but it is long and laborious (and to be honest... quite rubbish) and I would love to see what other people can come up with... something short and sweet that does the job and nothing more. When I set-up my code a couple of years ago I remember having all sorts of problems with US date format (I'm using UK - "dd/mm/yyyy"). I realise that I could set up a table and manually put the dates in, but I figure it can't be that hard to come up with this date according to the current month.

Any takers?

It is the logic used for our monthly refresh of data... Find first Sunday on or after the 6th of the Month?! (based on the current month). Once I have this date in a robust piece of code the world is my oyster!!

Thanks to everyone that gives this a go.

Cheers,

J.

 

[Mile-O]

Just use a loop to cycle through the seven days on or after the 6th, checking if its a Sunday. Break the loop if it is.

 

[Jibbadiah]

I found an old post from Bat17 that helped a lot... so have come up with the following...

Dim dDate As Date
dDate = DateSerial(Year(Date), Month(Date), 7)
    Do While WeekDay(dDate, 1) <> 1
        dDate = dDate + 1
    Loop
    
dDate = Format(dDate, "dd/mm/yyyy")
MsgBox dDate

Also used the following to test the logic and alleviate problems with querying in US date format... (think it came from ghudson originally?... but not sure.)

Public Function qd(ByVal dteDate As Date) As String
    qd = Format(dteDate, "mm/dd/yyyy")
End Function

 

[Jibbadiah]

OOPs... meant that to be the 6th... lol... doh!!