Access Query 2007

[sao123]

So it seems tha the math in excel is completely broken or unreliable... so i can use it to replicate calculation in access.

I need to figure out how to take any random date, then calculate the date of the 1st monday of that year.

i know that this formula works in excel

=A1-DAY(A1)+1+MOD(-WEEKDAY(A1-DAY(A1)+1,3),7)
Where A1 is a cell reference containing the random date.

However, the weekday and mod functions are different form excel to access.

How do i build this in access?

 

[PhatoseAlpha]

I believe the relevant function in Access 2007 is Datepart.

Datepart("w", theDate) will return which day of the week theDate is, with 1 as sunday and 7 as saturday.

Datepart("yyyy", theDate will return the year of theDate.

Those two functions should be usable to get your desired Date.

 

[sao123]

I believe the relevant function in Access 2007 is Datepart.

Datepart("w", theDate) will return which day of the week theDate is, with 1 as sunday and 7 as saturday.

Datepart("yyyy", theDate will return the year of theDate.

Those two functions should be usable to get your desired Date.

im aware of that... however the calculation which gives me that date doesnt work in access like it does in excel.

Forget all of the above... how do you calculate the date of the first monday in a given year?

 

[KLin]

That formula only gets me the first monday for the month, not the year.

Public Function GetFirstMonday(dDate As Date) As String
Dim lYear As Long
Dim i As Long
Dim chkDate As Date

lYear = DatePart("yyyy", dDate)
For i = 0 To 7
    chkDate = DateAdd("d", i, CDate("01/01/" & lYear))
    If Weekday(chkDate) = 2 Then
        GetFirstMonday = "First Monday is: " & DateAdd("d", i, CDate("01/01/" & lYear))
        i = 7
    End If
Next i

End Function

That's how I would get the first monday of a year in VBA.

Put that function into a module, save it, and in a query, put in "FirstMonday: GetFirstMonday(#4/26/2011#)" w/o the double quotes.

 

[sao123]

That formula only gets me the first monday for the month, not the year.

this isnt a problem since i know that january is always the first month of the year...
so the equation give me the first monday of

dateserial([any year],Jan,1)