MS ACCESS date range query using current month to work out last month

So today is 12/08/2011 and I want to query my database for records where the date is in the previous month.

I'm thinking there is something similar to this idea
where TRANS_DATE between datereltoday(-30) And datereltoday(0)
markloessiAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
why did you not mentioned in your original post?

where TRANS_DATE between format(dateserial(year(date()),month(date())-1,1),"yyyymmdd") and  format(dateserial(year(date()),month(date()),0),"yyyymmdd")
0
 
Rey Obrero (Capricorn1)Commented:


where TRANS_DATE between date() and dateadd("d",-30,Date())
0
 
Rey Obrero (Capricorn1)Commented:
if you want the records from the 1st of the previous month to todays date

where TRANS_DATE between dateserial(year(date()),month(date())-1,1) and  Date()
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
markloessiAuthor Commented:
I'm looking for something that will be relevant no matter the current date, aka it'll always give me a date range of 'last month' whether today is the 12th or 30th of the next month and whether last month was 30 or 31 days. I'd like to include this in a report query that will run on demand but not require any input by the requestor of the report.

both of the above appear to require inputs, unless I'm misinterpreting something.
0
 
Rey Obrero (Capricorn1)Commented:
by <date range of 'last month' > 
do you mean from the 1st to the last day of the month ?
0
 
markloessiAuthor Commented:
yep
0
 
Rey Obrero (Capricorn1)Commented:


where TRANS_DATE between dateserial(year(date()),month(date())-1,1) and  dateserial(year(date()),month(date()),0)
0
 
markloessiAuthor Commented:
hmm... that's not working the format of the data is yyyymmdd and it's stored as Text?
0
 
markloessiAuthor Commented:
Yes sorry on that score, this database is really a joke in some respects, every once i a while you run across something screwy that's in consistent within the tables, this happened to be one of those times, it didn't even ocurr to me to look until I started trouble shooting these query's. Thanks for hanging in there with me, the other formats will no doubt be useful to others.
0
 
markloessiAuthor Commented:
To others who find this solution and are looking for an answer: The other offerings in this sequence are good examples of dealing with dates as well, the chosen solution just matched the pecularity of my date data being stored as text.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.