Solved

Access question

Posted on 2013-02-04
4
238 Views
Last Modified: 2013-02-04
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.
0
Comment
Question by:Keef4000
  • 3
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38852130
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
 

Author Closing Comment

by:Keef4000
ID: 38852144
Thank you that was simple enough
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38852167
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38852196
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now