How can I get Month-To-Date range with only selecting current date

  • Thread starter Thread starter Whitney
  • Start date Start date
W

Whitney

I would like to be able to pull a report using today's day and have it query
the current months data.

For example, I'm creating an overall head count report, which includes
several subreports references headcount numbers, however one subreport is for
Attrition and I only want to reference the current MTD attrition. How can I
have it reference's today's date and provide the current MTD data, or use a
form to reference a date and have it return that current MTD data?

I know the basic concept of using this expression in the query
[Forms]![DailyReportDateSelector]![txtStartDate], I'm just not sure how to
tell it to query the whole month.
 
Add a field to your query ThisMonthYear:Month([DateFieldName]) &"/"&Year(
[DateFieldName])
(you dont even have to display it in the result) the criteria of which is
=Month([Forms]![DailyReportDateSelector]![txtStartDate])&"/"&Year([Forms]!
[DailyReportDateSelector]![txtStartDate])

While you could use Month(Date()), referencing the form and using both month
and year allows you to look at previous months and years if necessary.

I would like to be able to pull a report using today's day and have it query
the current months data.

For example, I'm creating an overall head count report, which includes
several subreports references headcount numbers, however one subreport is for
Attrition and I only want to reference the current MTD attrition. How can I
have it reference's today's date and provide the current MTD data, or use a
form to reference a date and have it return that current MTD data?

I know the basic concept of using this expression in the query
[Forms]![DailyReportDateSelector]![txtStartDate], I'm just not sure how to
tell it to query the whole month.
Click to expand...
 
First day of current month
DateSerial(Year(Date()),Month(Date()),1)
Last day of current month
DateSerial(Year(Date()),Month(Date())+1,0)

If you want to use a reference on a form to get the begin and end of
that month, replace the Date() function with a reference to the control
on the form.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
ThisMonthYear:Month([DateFieldName]) &"/"&Year([DateFieldName])
This is saying invalid expression.
My date field name is End Date, I replaced that for DateFieldName, but it's
still not working. I'm not sure what I'm doing wrong.
 
Whitney,

If you are using a query to identify the records, then you need to put
the criteria under End Date in the query.

Field: End Date
Criteria: Between DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0)

That criteria goes all on one line - in one criteria "cell".

For today (and the rest of the month of March 2008), that would return
all records where the End date was between 1 March 2008 and 31 March 2008.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

ThisMonthYear:Month([DateFieldName]) &"/"&Year([DateFieldName])
This is saying invalid expression.
My date field name is End Date, I replaced that for DateFieldName, but it's
still not working. I'm not sure what I'm doing wrong.


Whitney said:
I would like to be able to pull a report using today's day and have it query
the current months data.

For example, I'm creating an overall head count report, which includes
several subreports references headcount numbers, however one subreport is for
Attrition and I only want to reference the current MTD attrition. How can I
have it reference's today's date and provide the current MTD data, or use a
form to reference a date and have it return that current MTD data?

I know the basic concept of using this expression in the query
[Forms]![DailyReportDateSelector]![txtStartDate], I'm just not sure how to
tell it to query the whole month.
Click to expand...
Click to expand...
 
Back
Top