MTD Total as of Date Filtered

  • Thread starter Thread starter Pulling Hair Out
  • Start date Start date
P

Pulling Hair Out

I have a report with a "Date" column and a "MTD" column. The user is
prompted to enter a single date. i want the MTD column to sum the data from
the first of the month (of the date entered) to the date entered. Currently,
it is summing the whole month. Any ideas? Thanks in advance.
 
Sum what "data"? Are there some actual table and field names? Is the a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a single
date]))+1 And [Enter a single date])*[Freight])
 
SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based on. On
open, the report will prompt the user for a [Date], the report will show the
Forecast number for that date along with the MTD total up to the entered
date. Same with the Contacts Received. How can I tell the MTD total to only
go to the entered date? Thanks for your help.

Duane Hookom said:
Sum what "data"? Are there some actual table and field names? Is the a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
I have a report with a "Date" column and a "MTD" column. The user is
prompted to enter a single date. i want the MTD column to sum the data from
the first of the month (of the date entered) to the date entered. Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
 
If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based on. On
open, the report will prompt the user for a [Date], the report will show the
Forecast number for that date along with the MTD total up to the entered
date. Same with the Contacts Received. How can I tell the MTD total to only
go to the entered date? Thanks for your help.

Duane Hookom said:
Sum what "data"? Are there some actual table and field names? Is the a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
I have a report with a "Date" column and a "MTD" column. The user is
prompted to enter a single date. i want the MTD column to sum the data from
the first of the month (of the date entered) to the date entered. Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
This is perfect. THANK YOU for your idea and time.

Duane Hookom said:
If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based on. On
open, the report will prompt the user for a [Date], the report will show the
Forecast number for that date along with the MTD total up to the entered
date. Same with the Contacts Received. How can I tell the MTD total to only
go to the entered date? Thanks for your help.

Duane Hookom said:
Sum what "data"? Are there some actual table and field names? Is the a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


:

I have a report with a "Date" column and a "MTD" column. The user is
prompted to enter a single date. i want the MTD column to sum the data from
the first of the month (of the date entered) to the date entered. Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Duane, I'm sorry to bother you again. This was working fine when I had the
text boxes on a form of their own. Thought I would get creative and put the
text boxes on the unbound form "Main Menu". That way I could have the
command buttons open the report after the date is inputted. However, when I
do this I am getting #Error.

txtdate is an unbound text box where the user enters a date.
begmonthdate is an unbound text box (hidden) which should display the first
day of the month from txtdate using this: =[txtDate]-Day([txtDate])+1

Is there a reason this will not work on a different form?




Duane Hookom said:
If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based on. On
open, the report will prompt the user for a [Date], the report will show the
Forecast number for that date along with the MTD total up to the entered
date. Same with the Contacts Received. How can I tell the MTD total to only
go to the entered date? Thanks for your help.

Duane Hookom said:
Sum what "data"? Are there some actual table and field names? Is the a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


:

I have a report with a "Date" column and a "MTD" column. The user is
prompted to enter a single date. i want the MTD column to sum the data from
the first of the month (of the date entered) to the date entered. Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Where do you see #Error?

--
Duane Hookom
MS Access MVP


Pulling Hair Out said:
Duane, I'm sorry to bother you again. This was working fine when I had
the
text boxes on a form of their own. Thought I would get creative and put
the
text boxes on the unbound form "Main Menu". That way I could have the
command buttons open the report after the date is inputted. However, when
I
do this I am getting #Error.

txtdate is an unbound text box where the user enters a date.
begmonthdate is an unbound text box (hidden) which should display the
first
day of the month from txtdate using this: =[txtDate]-Day([txtDate])+1

Is there a reason this will not work on a different form?




Duane Hookom said:
If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with
a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has
a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so
you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All
CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based
on. On
open, the report will prompt the user for a [Date], the report will
show the
Forecast number for that date along with the MTD total up to the
entered
date. Same with the Contacts Received. How can I tell the MTD total
to only
go to the entered date? Thanks for your help.

:

Sum what "data"? Are there some actual table and field names? Is the
a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders
table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a
single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


:

I have a report with a "Date" column and a "MTD" column. The user
is
prompted to enter a single date. i want the MTD column to sum the
data from
the first of the month (of the date entered) to the date entered.
Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
In the begmonthdate txt box after a date is entered in the txtdate box.

Duane Hookom said:
Where do you see #Error?

--
Duane Hookom
MS Access MVP


Pulling Hair Out said:
Duane, I'm sorry to bother you again. This was working fine when I had
the
text boxes on a form of their own. Thought I would get creative and put
the
text boxes on the unbound form "Main Menu". That way I could have the
command buttons open the report after the date is inputted. However, when
I
do this I am getting #Error.

txtdate is an unbound text box where the user enters a date.
begmonthdate is an unbound text box (hidden) which should display the
first
day of the month from txtdate using this: =[txtDate]-Day([txtDate])+1

Is there a reason this will not work on a different form?




Duane Hookom said:
If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with
a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has
a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so
you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


:

SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All
CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based
on. On
open, the report will prompt the user for a [Date], the report will
show the
Forecast number for that date along with the MTD total up to the
entered
date. Same with the Contacts Received. How can I tell the MTD total
to only
go to the entered date? Thanks for your help.

:

Sum what "data"? Are there some actual table and field names? Is the
a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders
table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a
single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


:

I have a report with a "Date" column and a "MTD" column. The user
is
prompted to enter a single date. i want the MTD column to sum the
data from
the first of the month (of the date entered) to the date entered.
Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
So you have an unbound text box
Name: txtDate
Format: Short Date
Control Source:
And another text box:
Name: begmonthdate
Format: Short Date
Control Source:=[txtDate]-Day([txtDate])+1
And BegMonthDate is displaying #error?

I just tested this and the above setup works exactly how I would expect it to.


--
Duane Hookom
Microsoft Access MVP


Pulling Hair Out said:
In the begmonthdate txt box after a date is entered in the txtdate box.

Duane Hookom said:
Where do you see #Error?

--
Duane Hookom
MS Access MVP


Pulling Hair Out said:
Duane, I'm sorry to bother you again. This was working fine when I had
the
text boxes on a form of their own. Thought I would get creative and put
the
text boxes on the unbound form "Main Menu". That way I could have the
command buttons open the report after the date is inputted. However, when
I
do this I am getting #Error.

txtdate is an unbound text box where the user enters a date.
begmonthdate is an unbound text box (hidden) which should display the
first
day of the month from txtdate using this: =[txtDate]-Day([txtDate])+1

Is there a reason this will not work on a different form?




:

If the query only returns records for a specific date then, you need some
method to return the sum of other dates. This is can be accomplished with
a
subquery or joining to a totals query that returns the totals.

IMO, don't use parameter prompts in your queries. Always use controls on
forms for user interaction. I would create a form (frmPHODates) that has
a
visible text box (txtDate) to enter the date. Use another text box to
calculate the beginning of the month.
Name: txtMthBegDate
Control Source: =[txtDate]-Day([txtDate])+1

Then you totals query would have a criteria under the date field of:
Between [Forms]![frmPHODates]![txtMthBegDate] And
[Forms]![frmPHODates]![txtDate]
Sum or count or whatever your Forecast and Contracts Received fields.

Save the totals query and add it to the record source of your report so
you
can include the aggregated values from the totals query.

--
Duane Hookom
Microsoft Access MVP


:

SELECT [All CT qry with ADH].Date, [All CT qry with ADH].Forecast, [All
CT
qry with ADH].[Contacts Received]
FROM [All CT qry with ADH];

Above is a simplified example of the query which the report is based
on. On
open, the report will prompt the user for a [Date], the report will
show the
Forecast number for that date along with the MTD total up to the
entered
date. Same with the Contacts Received. How can I tell the MTD total
to only
go to the entered date? Thanks for your help.

:

Sum what "data"? Are there some actual table and field names? Is the
a SQL
view that you could share from the report's record source?

This is an expression that could be used in a report of the Orders
table in
Northwind ot get the MTD freight:

=Sum(Abs([OrderDate] Between ([Enter a single date]-Day([Enter a
single
date]))+1 And [Enter a single date])*[Freight])

--
Duane Hookom
Microsoft Access MVP


:

I have a report with a "Date" column and a "MTD" column. The user
is
prompted to enter a single date. i want the MTD column to sum the
data from
the first of the month (of the date entered) to the date entered.
Currently,
it is summing the whole month. Any ideas? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top