Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I select last date of previous month?

Posted on 2014-07-31
7
Medium Priority
?
338 Views
Last Modified: 2014-08-01
How do I alter the below to select last day of previous month rather than current date?

0-365: Sum(((IIf([ORDER_DATE]>=Date()-365,[AMOUNT_BILLED],0))))
0
Comment
Question by:garyrobbins
7 Comments
 
LVL 75
ID: 40232823
The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

mx
0
 
LVL 75
ID: 40232828
And this should come in handy:

Functions for calculating and for displaying Date/Time values in Access:

http://support.microsoft.com/kb/210604

mx
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40232834
First day of current month can be found with DateSerial(Year(Date()), Month(Date()), 1)

Last day of previous month is DATEAD that -1

Kelvin
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 780 total points
ID: 40232867
So I guess:

0-365: Sum(((IIf([ORDER_DATE]>= DateSerial(Year(Date()), Month(Date()),0) ,[AMOUNT_BILLED],0))))
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 780 total points
ID: 40233083
Previous comments look fine.
Just to clarify the concept and avoid remebering functions.

Required date: DateSerial (year, month, day)

In general,
1.  if day is set to 0 then it borrows 1 month. The month value is reduced by 1. The days added are those corresponding to the number of days in new month. If the month value becomes 0 then it is treated as in in next step.
2. If month is 0, then a year (12 months) is add to month value. The year value is reduced by 1.
Examples: Remember, calculating age
   DateSerial(2014,1,0)     ===>   2013, 12, 31
                                           0 days + 31 because the month will be 12, month 0  borrows 12 month from year part.
                                           0+12 months borrowing 1 year, the year becomes 2013
   DateSerial(2014,0,0)     ===>   2013, 11, 30

Now this should be obvious:
   DateSerial (currentYear, CurrentMonth, 0) ===> last of previous month - If currentMonth is 11 then result is last month of 10.
  DateSerial (currentYear, CurrentMonth, 1) ===> first of current month unless currentMonth =0, where it will be 1irst of December of last previous year.
0
 

Author Closing Comment

by:garyrobbins
ID: 40234098
Thank you both, first for putting in the thread like that, Joe and the explanation HNASR.  Working Great.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40234527
Welcome!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

578 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