Problem with Query Calculation

[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

 

[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

 

[Rene]

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

 

[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

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

 

[Rene]

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

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

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

 

[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

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

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

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