Solved

Access question

Posted on 2013-02-04
4
236 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

20 Experts available now in Live!

Get 1:1 Help Now