Solved

Access question

Posted on 2013-02-04
4
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

726 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