MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

[ashfaqahmedr]

Function for get Thursday as first day of week in current week month, this function returning first week as Thursday but first week of month in last month, But K need it will return only current month. For example if 1st date of month is on Tuesday it will return 1st date of month.


Function GetFirstofWeek(dtDate) As Date

'=MAX(C3-WEEKDAY(C3,14)+1, EOMONTH(C3, -1)+1)
GetFirstofWeek = DateAdd("d", dtDate, -Weekday(dtDate, vbThursday) + 1)
End Function
Function for get Wednesday as last day of week in current week month, this function returning last week as Wednesday but last week of month in next month, But I need it will return only current month. For example if last date of month is on Monday it will return 30th date of current month.
Function GetLastofWeek(dtDate As Date)
'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function
This function will return last date of month
Public Function EOMonth(RefDt As Date, Optional Months As Integer = 0) As Date
On Error GoTo Error_Handler
'DateSerial(Year([TxtDate]),Month([TxtDate])+1,0)

EOMonth = DateSerial(Year(RefDt), Month(RefDt) + Months + 1, 0)
End Function

This function will return first date of month
Public Function SOMonth(RefDate As Date, Optional Months As Integer = 0) As Date
On Error GoTo Error_Handler
'DateSerial(Year([TxtDate]),Month([TxtDate])-0,0)+1
SOMonth = DateSerial(Year(RefDate), Month(RefDate) + Months - 0, 0) + 1

 

[welshgasman]

From the debug window

? dateserial(Year(date),month(date)+1,0)
31/10/2018 
? dateserial(Year(date),month(date),1)
01/10/2018

Why are you trying formulae in Excel when you want an Access function and this is an Access forum?

I would get the weekday of the first of the month.
If not a Thursday, calculate the date for the previous Thursday and then use last date of that month.
That is your first week.
Loop and add 7 to the start date until the end date is in the next month and day is less than 7 , to account for a Wednesday as last day of month like this month.
Again adjust the end date for this month, and then the start date for the next month.

Take it all in small steps, one at a time.