Limiting DSum results

[Guest]

I defined a text box using the Dsum function as follows:

=DSum("[Distance]","Hikes","[HikeId] <= " & [Forms]![RunningSums]!HikeId)

It works well however, it only works when viewing the entire data-set. The
data-set contains data for 6 years. If I enter a query such as: DateClimbed
greater than 1/1/05; the resulting records are for only this year but the
running sum is for all records in the table. I want to see the running totals
for this year alone. Suggestions?

 

[Guest]

The DSum will return the correct value based on the criteria. There has to
be a syntax error or a logic error that is causing the problem.

Is DateClimbed a date type field and have you tried this?
Also your criteria syntax is correct provided [Forms]![RunningSums]!HikeId
is a numeric field.

=DSum("[Distance]","Hikes","[HikeId] <= " & [Forms]![RunningSums]!HikeId _
& " And [DateClimbed] >= #1/1/2005#")

If [Forms]![RunningSums]!HikeId is a text field:
=DSum("[Distance]","Hikes","[HikeId] <= '" & [Forms]![RunningSums]!HikeId _
& "' And [DateClimbed] >= #1/1/2005#")

 

[Guest]

It worked like a charm. Thank You.

The DSum will return the correct value based on the criteria. There has to
be a syntax error or a logic error that is causing the problem.

Is DateClimbed a date type field and have you tried this?
Also your criteria syntax is correct provided [Forms]![RunningSums]!HikeId
is a numeric field.

=DSum("[Distance]","Hikes","[HikeId] <= " & [Forms]![RunningSums]!HikeId _
& " And [DateClimbed] >= #1/1/2005#")

If [Forms]![RunningSums]!HikeId is a text field:
=DSum("[Distance]","Hikes","[HikeId] <= '" & [Forms]![RunningSums]!HikeId _
& "' And [DateClimbed] >= #1/1/2005#")

I defined a text box using the Dsum function as follows:

=DSum("[Distance]","Hikes","[HikeId] <= " & [Forms]![RunningSums]!HikeId)

It works well however, it only works when viewing the entire data-set. The
data-set contains data for 6 years. If I enter a query such as: DateClimbed
greater than 1/1/05; the resulting records are for only this year but the
running sum is for all records in the table. I want to see the running totals
for this year alone. Suggestions?