I'm working on automating some processes for a company using MS Access to control Excel. One of the reports depends on when direct deposits happen in a month. A month can have two or three two-week pay periods depending on when the direct deposit dates fall. For example, in 12/22 there were two direct deposit dates of 12/14 and 12/28. (The pay period ending dates are always exactly seven days prior to the direct deposit date.) In 11/22, there were three direct deposit days of 11/2, 11/16 and 11/30. This made one of the November pay period ending date fall in October since it was a week before 11/2.
What I'm trying to do is create a function that will return the first direct deposit date in any particular month of any year. I know the last direct deposit date of 2022 was 12/28 so I think the function will need to continue to add 14 days to 12/28/22 until it finds the sought after month in the sought after year. This is what I have so far:
This won't work, however, forever. I think the function needs to be recursive but I'm not sure.Code:Private Function FindFirstDDinMonth() As Date Dim i As Integer Dim Target Month As Integer Const IncrementDays As Integer = 14 Target Month = Month(DateAdd("m", -1, ReportCreationDate)) For i = 1 To 12 If Month(DateAdd("d", i * IncrementDays, #12/28/2022#)) = Target Month Then FindFirstDDinMonth = DateAdd("d", i * IncrementDays, #12/28/2022#) Exit For End If Next i End Function
Thanks very much for your help.