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
Medium Priority
338 Views
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
Question by:garyrobbins

LVL 75

Expert Comment

ID: 40232823
The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

mx
0

LVL 75

Expert Comment

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

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

LVL 75

Accepted Solution

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

hnasr earned 780 total points
ID: 40233083
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

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

ID: 40234527
Welcome!
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll