Access SQL query – select date range from specific day of the month

I have been scratching my head over this for a while, but i would like to make a query that selects values from the 24th of the current / last month to the 24th of the next/current month depending on the month we are in – this is basically from one payroll to the next. any suggestions on the SQL query?

So for instance – if the current date is the 20th March then i would want all data from 24th Feb to the 24th March, but if the date was over the 24th of this month say 30th March then i would want data from 24th March to 24th April – if that makes sense.

Thanks, Phill

Answer

With this expression:

iif(
  day(date()) >= 24,
  dateserial(year(date()), month(date()), 24),
  dateserial(year(dateadd('m', -1, date())), month(dateadd('m', -1, date())), 24)
)

you can get the starting date of your condition (though I’m not sure about the >= sign, maybe it should by just >? ), so use it like this:

select t.col1, t.col2, ... 
from (
  select *, 
    iif(
      day(date()) >= 24,
      dateserial(year(date()), month(date()), 24),
      dateserial(year(dateadd('m', -1, date())), month(dateadd('m', -1, date())), 24)
    ) as start_date
  from tablename
) as t
where t.date_column between t.start_date and dateadd('m', 1, t.start_date)