How To: Build MS Access query to return date of 2nd Saturday from Current Date?-CodePudding

I'm trying to build a query or VBA code (which ever works best) to return the date for the 2nd Saturday from the current date.

Example: Today is Friday, February 3rd, 2023 (02/03/23). The automatic query should return Saturday, February 11th. This would be calculating the firstdayofweek argument used in the WEEKDAY Function (used in MS Excel and Access) based on the current date.

I've just not been able to build the correct query where it calculates this specific date in time based on the current date.

Any help would be greatly appreciated!

Thanks, Matt

CodePudding user response：

The structure is going to be '15 days after the friday before today' (originally I thought '14 days after the saturday before today', but that misfires if called on saturdays)

In VBA:

Function SecondSat(Optional DateFrom As Date) As Date
If DateFrom = 0 Then DateFrom = Date 'default to today's date
SecondSat = DateFrom - DatePart("w", DateFrom, vbSaturday)   15
End Function

in MS Access query language:

SELECT Date() - DATEPART('w', Date(), 7)   15

CodePudding user response：

You can use my generic function DateNextWeekday found in my library at GitHub: VBA.Date

In code:

NextNextSaturday = DateAdd("ww", 1, DateNextWeekday(Date, vbSaturday))

In a query:

NextNextSaturday: VDateAdd("ww",1,DateNextWeekday(Date(),7))