DSum not updating records due to Type Conversion failure

S

Spanky

I am having a really hard time trying to get my DSum update query to
work. What I'm trying to accomplish is a running total of claims
across time for claimants. The fields I'm using in my update query
are [CCC] a text field that identifies a claimant, [SumOfPAID_BY_US] a
number that is monthly summation of all claims. I have created a new
field called [Running Total Claims] a number that will be updated by
my query.

I have a few questions
A. Is Dsum the best function to use for a running total across time
B. My current query doen'st account for time: UPDATE [tbl_Over Spec
Deduct] SET [tbl_Over Spec Deduct].[Running Total Claims] =
DSum("[tbl_Over Spec Deduct]![SumOfPAID_BY_US]","tbl_Over Spec
Deduct"," [tbl_Over Spec Deduct]![CCC]=" & [CCC]);
I have a month field in my table, how can I create a running total
based on [CCC] and my month field?

C. Any ideas as to why this my cause a Type conversion failure


Thanks so much

David Hallidy
 
G

Guest

I'm not convinced you really need a "Running_Sum" field in your table.

It is almost never a good idea to store calculated data in a field of its
own. Instead, you could use a query to generate this data whenever you need
it. This option is much safer, as there is no way within Access to ensure
that if a value in a record is changed, all of the other dependent values
will also be changed.

Why do you need this? If you need this value in a report, you can set the
controls RunningSum property (it's on the data tab) to Over All or Over
Group, and that will provide a running sum of a field in the report.

The other problem I see is that although you have a month field, you don't
appear to have a year field in your table, so summing across all months prior
to this month
is not possible.

If you had a [SomeYear] field as well as a [SomeMonth] (don't use Year and
Month as field names), and absolutely must do this, try:

Update [tbl_Over_Spec_Deduct]
SET [Running_Total_Claims] =
DSUM("SumOfPaid_By_US", "tbl_Over_Spec_Deduct",
"[CCC] = " & tbl_Over_Spec_Decuct.CCC _
& " AND DateSerial([SomeYear], [SomeMonth] +1, 0) < #" & Date() & "#")

Or something along these lines.

HTH
Dale
 

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