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.


Whitney wrote:
>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.

-- 
Message posted via http://www.accessmonster.com

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
'====================================================


Whitney wrote:
> 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.

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" wrote:

> 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.

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
'====================================================


Whitney wrote:
> 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" wrote:
> 
>> 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.