Access question

Is their an automatic function like "today" for something like "Previous Month"

We run reports all the time and we have to constantly put in between "1-1-12 and 1-31-12" It would be nice if there was something like "current month" or "previous month" sort of thing.
Keef4000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
No,  But you can define the current month like:

BETWEEN DateSerial(Year(Date()), Month(Date()), 1)
AND DateSerial(Year(Date()), Month(Date()) + 1, 0)

For previous month use:


BETWEEN DateSerial(Year(Date()), Month(Date())-1, 1)
AND DateSerial(Year(Date()), Month(Date()), 0)
0
 
Keef4000Author Commented:
Thank you that was simple enough
0
 
Dale FyeCommented:
I generally do this in the form of a combo box, where I display the month names, for maybe the last 3 or 4 months.

I use a table (tbl_Numbers) that has a single field (intNumber) and 10 records (0-9).  Then, I create a query that looks like:

SELECT Format(DateSerial(Year(Date()), Month(Date()) - intNumber, 1), "mmm")
             , DateSerial(Year(Date()), Month(Date()) - intNumber, 1) as StartDate
             , DateSerial(Year(Date()), Month(Date()) - intNumber + 1, 0) as EndDate
FROM tbl_Numbers
ORDER BY intNumber
0
 
Dale FyeCommented:
By doing the combo box, you can hide the start and end dates by setting the column widths to 0, but can refer to those columns in your code.

Note, that if you refer to these combo columns in code, you probably need to wrap them in the cDate( ) function to ensure they are treated as dates and not as text:

cDate(me.comboDates.column(1)) or cDate(me.comboDates.column(2))
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.