First Day of Next Month

[swalsh84]

I am working on a case tracking database and the client wants to know what cases were outstanding as of month end. I give them the option to select the month upon which they would like to report and then use this month and year selection to query the tables and come back with "Received", "Closed" and "Outstanding". Received and Closed are working just fine. Outstanding is always live data because I look for a [CaseStatus] <> "Closed".

They want to know what was outstanding as of month end not what is outstanding right now. I know that I need to compare the [ClosedDate] to the first day of the month after the month selection, but I don't know how to do this as all of the cases selected are "Closed" and therefore have a [ClosedDate].

For example, the client selects reporting for December. Outputs should be as follows:

1. Received in December
2. Closed in December
3. Outstanding as of December 31

Here's the bottom line for outstanding cases. If it has been closed after the last day of the selected month, exclude it. Otherwise include only those cases with a [CaseStatus] <> "Closed".

Any thoughts?

[swalsh84]

I figured out with some help how to get the results set correct with a hard-coded date, but now I'm stuck on how to get the proper dates plugged into the query. The project is confidential, otherwise I'd post it. I have a year selection and a month selection on a form. Year selections is in YYYY format. Month selection is in MMMM format.

If the user selects "2010" and "December", I'd like the following to happen:

CaseStatus     ReceivedDate     Closed Date
------------------------------------------------------
<> Closed      <#1/1/2011#     
Closed                          >#12/31/2010#

I'm having trouble building the section in the query that would allow for this. I'm open to writing a custom function to make it happen, but my programming skills are somewhat lacking in this area.

[SteveF]

I am working on a case tracking database and the client wants to know what cases were outstanding as of month end. I give them the option to select the month upon which they would like to report and then use this month and year selection to query the tables and come back with "Received", "Closed" and "Outstanding". Received and Closed are working just fine. Outstanding is always live data because I look for a [CaseStatus] <> "Closed".

They want to know what was outstanding as of month end not what is outstanding right now. I know that I need to compare the [ClosedDate] to the first day of the month after the month selection, but I don't know how to do this as all of the cases selected are "Closed" and therefore have a [ClosedDate].

For example, the client selects reporting for December. Outputs should be as follows:

1. Received in December
2. Closed in December
3. Outstanding as of December 31

Here's the bottom line for outstanding cases. If it has been closed after the last day of the selected month, exclude it. Otherwise include only those cases with a [CaseStatus] <> "Closed".

Any thoughts?

This can be a bit tricky, because the last day of the month isn't always the same: it can be 28, 29, 30 or 31, depending on the month and year.

Fortunately, there is a way to handle this: Access translates the "zeroth" day of any month into the last day of the preceding month, no matter what that date is, i.e. (in mm/dd/yyyy format) 01/00/2011 = 12/31/2010, 12/00/2010 = 11/30/2010, etc.

You may need to build some VBA code to handle this -- let me poke around in my archives & get back with more info; I know I have at least one former project where monthly reports were involved & I had to deal with this. More to come.

Steve

[SteveF]

I went digging in my archives, and found pay dirt!

This is code for a combo box control in a form from a weekly/monthly/yearly timesheet and time-budget application I built for a previous employer. Employees were expected to not only log their start/stop times at the beginning and end of day, lunch hours, etc., but also log time spent on specific tasks pertinent to the department in which they worked.

The form on which the combo resides is used to select a specific month for a report used by a department supervisor. The functions used here that are relevant to what you're trying to do are:

Month(),
Day() -- used in SQL Rowsource for the combo
Year(),
and most importantly: DateSerial()

Also potentially useful is: Weekday(), if you need to deal with forms, reports, etc. on a weekly basis

Look up these functions in your Access Visual Basic Help to understand how they work.

The controls on the form set Public Variables for the Start and End Dates of the report, which are then supplied by Public Functions to the Query which is the DataSource for the report. If you follow the code closely, you'll see that I use the DateSerial function to set the End Date for the report to Day 0 of the month following the one chosen by the user, which automagically handles the varying last-day-of-the-month problem.

Private Sub cboSelectMonth_AfterUpdate()
'set end date for running the report
Dim intYear As Integer, intMonth As Integer

intMonth = Month(Me!cboSelectMonth.Column(0))
intYear = Year(Me!cboSelectMonth.Column(0))
If intMonth = 12 Then
intYear = intYear + 1
intMonth = 1
Else
intMonth = intMonth + 1
End If

PubEndDate = DateSerial(intYear, intMonth, 0)

cmdTransfer.Enabled = True


End Sub


SQL Rowsource for Combo:
SELECT tblLocalDates.TableDate, Format([TableDate],"mm/yyyy") AS PickMonth
FROM tblLocalDates
WHERE (((tblLocalDates.TableDate) Between fStartDate() And fLogDate()) AND ((Day([tblLocalDates]![TableDate]))=1))
ORDER BY Format([TableDate],"yyyy/mm") DESC;

One thing I did in this project -- which you may choose to do as well -- was to build a table of dates; the date range in this table goes daily from 29 Oct 1995 to 12 Jan 2082. Since this was too many to enter by hand, I built a quick little VB routine to loop approx. 32,000 times and insert the next sequential date into the table.

I found the date table useful as a resource for setting date ranges in forms, queries and reports; the SQL Rowsource for the combo uses this table to build a list of months from which to choose. Restricting the dates in the combo to the first of each month {Day([tblLocalDates]![TableDate]))=1} presents a list of months {Format([TableDate],"mm/yyyy")} for the user to select without having a long list of duplicate entries.

[weekend00]

This can be a bit tricky, because the last day of the month isn't always the same: it can be 28, 29, 30 or 31, depending on the month and year.

Fortunately, there is a way to handle this: Access translates the "zeroth" day of any month into the last day of the preceding month, no matter what that date is, i.e. (in mm/dd/yyyy format) 01/00/2011 = 12/31/2010, 12/00/2010 = 11/30/2010, etc.

You may need to build some VBA code to handle this -- let me poke around in my archives & get back with more info; I know I have at least one former project where monthly reports were involved & I had to deal with this. More to come.

Steve

Hi Steve, I tried expression #1/0/2010# and cdate("1/0/2010") in Access 2003, both gave me error message.

maybe it only work in Access 2010?

[SteveF]

Hi Steve, I tried expression #1/0/2010# and cdate("1/0/2010") in Access 2003, both gave me error message.

maybe it only work in Access 2010?

I may have been mistaken about the exact format. As in my most-recent-to-this post, what does work (in all versions since at least Access97) is:

DateSerial(2010,1,0)

This gets calculated out to be 12/31/2009.

[weekend00]

Yes, DateSerial(2010,1,0) does make 12/31/2009