Records from last month

[KoolBreeze]

I'm trying to write a dynamic query to retrieve records entered into a table during the previous month, e.g. between 2/1/2018 and 2/28/2018. I'm having a little trouble getting the datediff and dateadd functions to work as expected.

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

The above statements return the first day and last day of the previous month respectively, 2/1/2018 and 2/28/2018, in SQL Server.

select DATEADD("M", DATEDIFF("M", 0, DATE())-1, 0)
select DATEADD("M", DATEDIFF("M", -1, DATE())-1, -1)

So, why don't the above statements return do the same in Access 2010? Instead both statements return 2/28/2018.



Thanks for the help.

[ranman256]

make a form,
on the form, put 4 text boxes, txtStart, txtEnd, txtPrevStart, txtPrevEnd
when user sets the txtStart, the formula for txtPrevStart is =DateAdd("m",-1,txtStart)
txtPrevEnd is =DateAdd("m",-1,txtEnd)

then some queries can run current month:
select * from table where [date] between forms!myForm!txtSTart and forms!myForm!txtEnd

and others the previous
select * from table where [date] between forms!myForm!txtPrevStart and forms!myForm!txtPrevEnd

you can even make quick picks to set the start end date boxes ....

[KoolBreeze]

Not really looking for anything that involved. I have a simple report that for the records to be listed on and just want to link the report to a query that I don't have to manually change the dates on to get the previous month's entries. So in the end, all the user has to do is click report and last month's records are displayed.

[KoolBreeze]

I found another way shortly after posting:

SELECT EntryDate
FROM tRecords
WHERE EntryDate >= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND EntryDate <= DateSerial(Year(Date()), Month(Date()), 0)

Still not sure why the other way works on SQL Server but not Access though.

[orange]

You might want to review the Between operator, also.