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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.