SQL: First and Last Date of Previous Month

I am trying to make database connections in Excel but I keep getting the Failure to Refresh error. I have tried several things to get the stamp between the first and last date of the the previous month:

SELECT COUNT(*)
FROM MTS
WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C' AND (STAMP between DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP), '20001231') AND DATEADD(MONTH, DATEDIFF(MONTH, '20010101', CURRENT_TIMESTAMP), '20001201');

DateSerial(Year(Now), Month(Now)-1, 1) FDPMonth ,
DateSerial(Year(Now), Month(Now)-1, 1+30) FDCMonth

dateadd(m, datediff(m, #01/01/1900#, SYSDATE()-1)-1, #01/01/1900#) AND dateadd(m, datediff(m,#01/01/1900#, SYSDATE()-1), #01/01/1900#)-1

what am I doing wrong?

I am trying to get the first date of previous month ex: 6/1/2013 and last day of previous month: 6/30/2013
I need the SQL for this since I am using it as a data connection string.
My access which my excel is connecting to has stamps of all dates and I want to only extract the dates between first and last dates of previous months

For workbook connections- for data connection properties: command type: table/default/sql and then command text I put in my SQL. What should I be using instead?

Try this:Pls make sure your field names and table names are right in my code

SELECT COUNT(*)
FROM MTS WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C' AND format(STAMP,"mmyyyy")=format(DateAdd("m",-1,Now),"mmyyyy")
Arun M

This wouldn't work when you are in January 1 of next year. The previous month would be 0 and year would not grab stamps from last year. But the rest of the code worked!

It's still not working with the whole code. Any other ideas?

SELECT (*)
FROM MTS
WHERE TRAN_CODE like '%NA7' AND FROM_BLDG = 'C'
AND STAMP>=DATEADD("m",-1,DATE())-DAY(DATE())+1 AND STAMP< DATE()-DAY(DATE());

This worked - it had to do with the date formats within the datediff and dateadd functions.

select *
from mts
where tran_code like '%na7' and
from_bldg = 'c' and
datevalue(stamp)<dateadd("m",datediff("m",'1900-01-01', FORMAT(Now()-1,'YYYY-MM-DD')),'1900-01-01') and
datevalue(stamp)>=dateadd("m",datediff("m",'1900-01-01', FORMAT(Now()-1,'YYYY-MM-DD'))-1,'1900-01-01')

this will return all records from the previous month.

varying the number you add/subtract after the format statement in the datediff function will change which month you get.

+/- 0 = first day of current month
-1 = first day of last month
-2 = first day of month before previous month
+1 = first day of next month
+2 = first day of month after next

etc and so on and so on......

Manali, did you happen to try the solution on post #6? any issues?