Problem with Query Calculation

R

Rene

Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that fall
between the date ranges I specify on the parameter form. The problem is the
expression.

The expression provides a running (aggregate) total for each month. However,
it provides a total of ALL fields in the source table. I need the running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Please help.

Rene
 
V

vanderghast

The WHERE clause (column 6) is outside your DSum (column 5), in other
words, your DSum is not aware of your WHERE clause since that clause is out
or reach, for your DSum, so, try to add the WHERE condition into the DSum,
with an AND:

DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "' AND
([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )



Vanderghast, Access MVP
 
R

Rene

I tried it and it displays error when I run the query.

Any other suggestions.

vanderghast said:
The WHERE clause (column 6) is outside your DSum (column 5), in other
words, your DSum is not aware of your WHERE clause since that clause is out
or reach, for your DSum, so, try to add the WHERE condition into the DSum,
with an AND:

DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "' AND
([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )



Vanderghast, Access MVP


Rene said:
Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that fall
between the date ranges I specify on the parameter form. The problem is
the
expression.

The expression provides a running (aggregate) total for each month.
However,
it provides a total of ALL fields in the source table. I need the running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Please help.

Rene
 
V

vanderghast

Maybe luckier with:


DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & Format("mm",
[Begin] ) &
"' AND ([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )


where I simply replaced the computed expression, Month, by its expression.



Vanderghast, Access MVP



Rene said:
I tried it and it displays error when I run the query.

Any other suggestions.

vanderghast said:
The WHERE clause (column 6) is outside your DSum (column 5), in other
words, your DSum is not aware of your WHERE clause since that clause is
out
or reach, for your DSum, so, try to add the WHERE condition into the
DSum,
with an AND:

DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'
AND
([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )



Vanderghast, Access MVP


Rene said:
Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] &
"'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date]
And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that
fall
between the date ranges I specify on the parameter form. The problem is
the
expression.

The expression provides a running (aggregate) total for each month.
However,
it provides a total of ALL fields in the source table. I need the
running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Please help.

Rene
 
R

Rene

No luck with this expression. It is telling me it causes an error in a macro.

vanderghast said:
Maybe luckier with:


DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & Format("mm",
[Begin] ) &
"' AND ([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )


where I simply replaced the computed expression, Month, by its expression.



Vanderghast, Access MVP



Rene said:
I tried it and it displays error when I run the query.

Any other suggestions.

vanderghast said:
The WHERE clause (column 6) is outside your DSum (column 5), in other
words, your DSum is not aware of your WHERE clause since that clause is
out
or reach, for your DSum, so, try to add the WHERE condition into the
DSum,
with an AND:

DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'
AND
([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )



Vanderghast, Access MVP


Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] &
"'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start Date]
And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that
fall
between the date ranges I specify on the parameter form. The problem is
the
expression.

The expression provides a running (aggregate) total for each month.
However,
it provides a total of ALL fields in the source table. I need the
running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me everything.

Please help.

Rene
 
V

vanderghast

It says "macro"? so maybe the query is right and a 'macro' calling the
query has a problem? just guessing, at this point.

Vanderghast, Access MVP


Rene said:
No luck with this expression. It is telling me it causes an error in a
macro.

vanderghast said:
Maybe luckier with:


DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & Format("mm",
[Begin] ) &
"' AND ([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )


where I simply replaced the computed expression, Month, by its
expression.



Vanderghast, Access MVP



Rene said:
I tried it and it displays error when I run the query.

Any other suggestions.

:

The WHERE clause (column 6) is outside your DSum (column 5), in other
words, your DSum is not aware of your WHERE clause since that clause
is
out
or reach, for your DSum, so, try to add the WHERE condition into the
DSum,
with an AND:

DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] & "'
AND
([Begin] Between [Forms]![ Date Range]![Start Date] And
[Forms]![Date Range]![End Date] )" )



Vanderghast, Access MVP


Good afternoon,

The query has the following six columns (Totals Query):

Column 1 (Group By) Month: Format([Begin],"mm")

Column 2 (Group By) Year: Year([Begin])

Column 3 (Group By) [Parent Company]

Column 4 (Sum) [Hours]

Column 5 (Expression) Hours Ytd:
DSum("Hours","tbl_Manhours","Format([Begin], 'mm')<= '" & [Month] &
"'")

Column 6 (Where) [Begin] Between [Forms]![ Date Range]![Start
Date]
And
[Forms]![Date Range]![End Date]

The query provides a sum of the hours by month, for the months that
fall
between the date ranges I specify on the parameter form. The problem
is
the
expression.

The expression provides a running (aggregate) total for each month.
However,
it provides a total of ALL fields in the source table. I need the
running
total to be based ONLY on the date ranges I specify.

For instance, if I specify between 01-01-09 and 06-30-09, It should
aggregate the totals for these months only. And not give me
everything.

Please help.

Rene
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top