Populate table data include year months start and end (1 Viewer)

[ahmedjamalaboelez]

Good day my friends
I have this table in my financial periods details
YearID MonthID MonthStart MonthEnd
2022 01 01/01/2022 31/01/2022
2022 02 01/02/2022 28/02/2022

if i have the first date of the year ( 01/01/2022)
how could i populate 12 Records includes 12 months information same up illustrated data?

I tried this Code its work fine in access ! but not Work with Sql Server !! I think Because of how i calculate start and end date !!!

Dim rs     As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM fmonth_lines")
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst                              'Unnecessary in this case, but still a good habit
        Do Until rs.EOF = True
            Dim firstdate As String
            firstdate = "01" & "/" & Format(rs!fmonth_id, "00") & "/" & rs!fmonth_fyearid
            rs.Edit
            rs!fmonth_starts = DateSerial(Year(firstdate), month(firstdate), 1)
            rs!fmonth_ends = DateSerial(Year(firstdate), month(firstdate) + 1, 0)
            rs.Update
            rs.MoveNext
        Loop
    Else
    End If
    rs.Close                                   
    Set rs = Nothing

Thanks,
A.J

 

[Gasman]

Year() etc work on dates, not strings?

Edit: Well I am blowed seems to work?

tt="01/11/2022"
? tt
01/11/2022
? year(tt)
 2022 
? month(tt)
 11 
? day(tt)
 1

 

[MajP]

The MS date functions are pretty flexible

Year(date)

The required date argument is any Variant, numeric expression, string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

 

[Gasman]

The MS date functions are pretty flexible

Not something I would ever have dreamed of doing TBH

 

[MajP]

Even handles regional settings.
?month("01/11/2022")
1

 

[ahmedjamalaboelez]

 

[Kamayo Ako]

View attachment 104810

You're Dashboard looks elegant sir.