Set Criteria Date Range using Public Function

[DoneganF]

I need to limit my query/report output to only include a 90-day date range of a field I call “Post-Date”. The ending date for that range is the last day of the month of a field I call “Period”. The first day of the date range is 90 days earlier then that date. The function below successfully returns the last day of the month for the Period field. However, when I try to use the function in my query criteria it doesn’t return anything. Just below the function, I’ve also pasted the date range criteria that I entered for my “Post-Date” field. The expression doesn’t return anything as is

This is my first go at using public functions in queries, so please be as detailed as possible in your response. Your help is greatly appreciated!

Public Function LastDayOfMonth(D As Date) As Date

Dim LDOM As Date
Dim NM As Date

NM = DateAdd("m", 1, D)
LDOM = DateSerial(Year(NM), Month(NM), "1")
LDOM = DateAdd("d", -1, LDOM)

LastDayOfMonth = LDOM

End Function

DATE RANGE CRITERIA:
Between DateAdd("d",-90,LastDayOfMonth([Period])) And LastDayOfMonth([Period])

 

[CJ_London]

nothing wrong with your function, tho' could be a simpler one liner

dateadd("m",1,dateadd("d",-day(D),D)))

few things to check

1. your function is in a module?
2. period is populated with a value?
3. post-date has values within the required range?
4. your post-date does not include a time element? If so then you wont get any records returned for the last date of the range

 

[MajP]

Also to make things easier you can do a wrapper function where a function "wraps" another function

Public Function LastDayOfMonth(D As Date) As Date
  Dim LDOM As Date
  Dim NM As Date
  NM = DateAdd("m", 1, D)
  LDOM = DateSerial(Year(NM), Month(NM), "1")
  LDOM = DateAdd("d", -1, LDOM)
  LastDayOfMonth = LDOM
End Function
Public Function Get90Prior(D As Date) As Date
  Get90Prior = DateAdd("d", -90, LastDayOfMonth(D))
End Function

between Get90Prior([Period]) AND LastDayOfMonth([Period])

It is much easier to debug in vba than debug in sql

 

[DoneganF]

Thanks - this was very helpful