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)
I'm thinking there is something similar to this idea
where TRANS_DATE between datereltoday(-30) And datereltoday(0)
if you want the records from the 1st of the previous month to todays date
where TRANS_DATE between dateserial(year(date()),mo
where TRANS_DATE between dateserial(year(date()),mo
ASKER
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.
both of the above appear to require inputs, unless I'm misinterpreting something.
by <date range of 'last month' >
do you mean from the 1st to the last day of the month ?
do you mean from the 1st to the last day of the month ?
ASKER
yep
where TRANS_DATE between dateserial(year(date()),mo
ASKER
hmm... that's not working the format of the data is yyyymmdd and it's stored as Text?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
where TRANS_DATE between date() and dateadd("d",-30,Date())