Strange behavior of Dateadd function, drops the 31st day of a month

[Chris_Cline]

I have a query that looks for any invoices billed within a specific month in the previous year and also looks for any invoices billed during that same month in the current year.

The field [EffectiveDate] is the due date of an invoice.
Using a criteria as follows:
Between DateAdd("yyyy",-1,[Beginning Date]) And DateAdd("m",-11,[Beginning Date]-1) Or Between [Beginning Date] And DateAdd("m",1,[Beginning Date]-1)

Here is where it gets strange.
For example if you enter the parameter of [Beginning Date] as "6/1/21" it will return any invoices with an effective date between 6/1/20 and 6/30/20 or 6/1/21 and 6/30/21. Which is what I intended.


However, any months that have 31days do not give the same results. For some reason the 31st day is being dropped off and only invoices with an effective date between the first and the thirtieth days are returned.

Can anybody explain why this is happening?

Thank you in advance.

[moke123]

Have you tried dateserial() ?
Use the 0 day of the next month to get the last day of any month.

Not sure it will work but it's the first thing that came to mind.

something like (air code)

Between DateSerial(Year([BeginningDate])-1,month([Beginning Date]),1) And DateSerial(Year([BeginningDate]),month([Beginning Date]+1),0)

[pbaldy]

I would use this to get the last day of a month:

DateSerial(Year(dte), Month(dte) + 1, 0)

Given July:

?DateSerial(Year(#7/1/21#), Month(#7/1/21#) + 1, 0)
7/31/2021

Your method subtracts a day from 7/1 which is 6/30 and adds a month to get 7/30:

?DateAdd("m",1,#7/1/21#-1)
7/30/2021

[pbaldy]

Oops, my slow typing and copy/pasting rears its ugly head.

[moke123]

Oops, my slow typing and copy/pasting rears its ugly head.

so did my edit

[Chris_Cline]

Not what I was looking for. This returns everything from a starting point one year before the effective date till the day before the effective date.

What has me puzzled is that it only drops off the last day of the month for months that have 31 days months with 30 days work as expected. It does get me closer as the 31st days are coming up.

Thank you

[pbaldy]

Did you see "Your method subtracts a day from 7/1 which is 6/30 and adds a month to get 7/30"? Using DateSerial() will solve your problem.

[Welshgasman]

See if this enlightens you?

Sub TestBS()
Dim BeginningDate As Date

BeginningDate = #6/1/2020#
Debug.Print "DateAdd minus 1 year " & CStr(DateAdd("yyyy", -1, [BeginningDate]))
Debug.Print "DateAdd minus 11 months " & CStr(DateAdd("m", -11, [BeginningDate] - 1))
Debug.Print "Date - 1 " & BeginningDate - 1
Debug.Print "DateAdd 1 month less day " & CStr(DateAdd("m", 1, [BeginningDate] - 1))

BeginningDate = #7/1/2020#
Debug.Print "DateAdd minus 1 year " & CStr(DateAdd("yyyy", -1, [BeginningDate]))
Debug.Print "DateAdd minus 11 months " & CStr(DateAdd("m", -11, [BeginningDate] - 1))
Debug.Print "Date -1 " & BeginningDate - 1
Debug.Print "DateAdd 1 month less day " & CStr(DateAdd("m", 1, [BeginningDate] - 1))

End Sub

Result

DateAdd minus 1 year 01/06/2019
DateAdd minus 11 months 30/06/2019
Date - 1 31/05/2020
DateAdd 1 month less day 30/06/2020 You cannot have 31/06/20 ?
DateAdd minus 1 year 01/07/2019
DateAdd minus 11 months 30/07/2019
Date -1 30/06/2020
DateAdd 1 month less day 30/07/2020

[Chris_Cline]

Got it solved

Between
DateSerial(Year([Beginning Date])-1,Month([Beginning Date]),1)
And
DateSerial(Year([Beginning Date])-1,Month([Beginning Date])+1,0)
Or
Between
[Beginning Date]
And
DateSerial(Year([Beginning Date]),Month([Beginning Date])+1,0)

[Chris_Cline]

Thank you for showing me what the math was really doing. Resolved using the Dateadd function

Between
DateAdd("yyyy",-1,[Beginning Date]) And DateAdd("d",-1,(DateAdd("m",-11,[Beginning Date])))
Or Between
[Beginning Date] And DateAdd("d",-1,(DateAdd("m",1,[Beginning Date])))