Code to find first/last DDD in Year

[kirkm]

Hi,
Been trying a few things and failing. I need to find the Date of the
first and Last DDD in Year. Where DDD is the day of a date (passed into the function). Any help much appreciated.

 

[Auntiejack56]

So you need a function like
mydate = getFirst("Monday", 2017)
which returns the date of the first Monday in the Year?
And similarly for the last, say, Friday?

 

[kirkm]

Yes, both would be Monday in this example so I suppose last would be that -1 week ?

 

[Auntiejack56]

Hi, here's a start. Sorry, bit busy at the moment! You'll have to check it.

Function LastMonday(parYear as long) as date
LastMonday = DateAdd("d", -Weekday(dateserial(paryear+1,1,1), vbtuesday), dateserial(paryear+1,1,1))
End Function

 

[kirkm]

I don't quite follow the paryear+1,1 stuff yet but will analyse further! I have this so far for first day. NB I like your code much better but this is the only way I know how.

Function FirstDDD(x As String) As Date
    'x is a 4 digit year
    Dim db As DAO.Database, theDayWanted As Integer, DayMatch As Date
    Dim r As DAO.Recordset
    Set r = CurrentDb.OpenRecordset("Select [Date Entered] From tblMain Where Sheet = '" & Me!lstSheet & "' and [Date Entered] Like '*" & x & "*' Order by [Date Entered];")
    
    If r.RecordCount > 0 Then 'Date Found to get Day From
        r.MoveFirst
        theDayWanted = Weekday(r(0))
        DayMatch = CDate("01 Jan " & x)
        Do Until Weekday(DayMatch) = theDayWanted
            DayMatch = DateAdd("d", 1, DayMatch)
        Loop
        FirstDDD = DayMatch
    End If
    Set r = Nothing
End Function

One I get the Last day working I'll test against your Function LastMonday.
Thanks

 

[Auntiejack56]

It's the first of Jan in the following year. You are finding the Monday prior to that.

 

[Auntiejack56]

Some logic:

Last Monday logic.
What is the first day of the new Year? Easy, 01/01/(parYear+1)
Say that first day is a Monday, what day do we want to find? The Monday before ie -7 days.
Is there an easy way to do it? Yes, Monday is Day 7 whenever we start counting from Tuesday. That is, Weekday(anydate, vbTuesday) = 7 if anydate is Monday. Subtract 7 for the date you want.
Ok, what if 1/1/(parYear+1) is a Tuesday, smartypants, what then? Using exactly the same logic, weekday(anydate, vbTuesday) = 1. Subtract 1 for the date you want.
Ahem, and if 1/1/(parYear+1) is a Friday? Weekday(anydate,vbTuesday) = 4. Subtract 4 for the date you want.


First Monday logic.
What is the first day of the year? 1/1/paryear
Say the first day is a Monday, what day do we want to find? That same day, ie add 0 days.
Is there an easy way to do it? Yes, we can derive 0 from Monday by subtracting the day in the week from 7. That is, 7 - Weekday(anydate,vbTuesday). Add this for the date you want.
And so on for other possible days.

Function FirstMonday(parYear as long) as date
FirstMonday = DateAdd("d", 7-Weekday(dateserial(paryear,1,1), vbTuesday), dateserial(paryear,1,1))
End Function

Jack

 

[arnelgp]

here is a function that will return the first (mon or tue.. etc) on a
a specific year in particular sheet (optional)

to use:
Me.txtboxFirstDate = FirstDDD("Mon","2017",[lstSheet])
Me.txtboxLastDate = LastDDD("Mon","2017",[lstSheet])

Public Function FirstDDD(dateDDD As String, strYear As String, Optional lstSheet As String) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' purpose:
'
'        return the first DDD date ("Mon", "Tue", etc.) in the year (strYear).
'
' parameters:
'
'        dateDDD     = date in format DDD ("Mon", "Tue", ... etc)
'        strYear     = year in string format ("2016", "2017", ... etc)
'        lstSheet    = (optional) on your original code, pass sheet if
'                      you need to return date for a specifc sheet
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    FirstDDD = DMin("[Date Entered]", "tblMain", "[Sheet] Like '" & lstSheet & "*' And " & _
        "Format([Date Entered],'ddd')='" & dateDDD & "' And Year([Date Entered])=" & strYear)
End Function

Public Function LastDDD(dateDDD As String, strYear As String, Optional lstSheet As String) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' purpose:
'
'        return the last DDD date ("Mon", "Tue", etc.) in the year (strYear).
'
' parameters:
'
'        dateDDD     = date in format DDD ("Mon", "Tue", ... etc)
'        strYear     = year in string format ("2016", "2017", ... etc)
'        lstSheet    = (optional) on your original code, pass sheet if
'                      you need to return date for a specifc sheet
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    LastDDD = DMax("[Date Entered]", "tblMain", "[Sheet] Like '" & lstSheet & "*' And " & _
        "Format([Date Entered],'ddd')='" & dateDDD & "' And Year([Date Entered])=" & strYear)
End Function