Dmax search for maximum date returns final date in the table and doesn't consider the criteria-CodePudding

I have a MS Access front end with a SQL Server back end database. I have a table [Month End] with columns ID, [Month End Date].

In VBA, I am trying to find the maximum date that is before the next Sunday. I get the next Sunday as a date. The DMax always returns the last date in the table and doesn't seem to look at the criteria.

This code worked until I moved the back end to SQL Server.

Code:

if Weekday(Now()) < 3 Then
   NextSunday = DateAdd("d", 1 - Weekday(Now()), Date)
else
   NextSunday = DateAdd("d", 8-Weekday(Now()), Date)

nextMonthEnd = DMax("[Month End Date]", "[Month End]", "[Month End Date] < #" & NextSunday & "#")

CodePudding user response：

If you are looking for the last day of month, you can use this function

Public Function LastDayOfMonth(ByVal d As Date) As Date
    d = DateAdd("m", 1, d)
    d = DateSerial(Year(d), Month(d), 1)
    LastDayOfMonth = DateAdd("d", -1, d)
End Function

First it gets the first day of the next month and then subtracts one day to get the last day of the current month. This works independently of the number of days in a month.

CodePudding user response：

Try forcing a format on NextSunday:

NextSunday = DateAdd("ww", Abs(Weekday(Date, vbTuesday) >= vbTuesday), DateAdd("d", 7 - (Weekday(Date) - 1), Date))
NextMonthEnd = DMax("[Month End Date]", "[Month End]", "[Month End Date] < #" & Format(NextSunday, "yyyy\/mm\/dd") & "#")