running totals

[Mark Carlyle via AccessMonster.com]

I am trying to work with Dsum in a query... but I get a multiplication of the
data. If there are 3 positive numbers in the dsum it multiplies the data x 3
if 4 then x4 etc. I have no clue why this is happening and why it only
happens with the positive numbers, but I am frustrated with the problem.

the dsum is like this.... (all one line of course)

Daily Balance: Format(DSum([total],"Transactions","datepart('d',[Date])<=" &
[Aday] & " and datepart('m',[Date])<=" & [AMonth] & " and datepart('yyyy',
[Date])<=" & [Ayear] & " and [trustacctnumber]=" & [trustalias] & ""),"$0.00")


total is a calculated value of credit - debit on the query
trustalias is assigned trustacctnumber in the query. It is a 8 digit number

TIA

 

[Wayne Morgan]

The way you have this written, ADay, AMonth, and AYear are values from the
query's fields. You need to define fields in the Transactions table that the
value from these fields will limit. Is [Date] a field in Transactions? If
so, it may be easier to use:

"[Date]<=#" & DateSerial([AYear], [AMonth], [ADay]) & "# And
[TrustAcctNumber]=" & [TrustAlias])

You also have two "" after [TrustAlias]. Is [TrustAlias] a number or string?
Is [TrustAcctNumber] a number or string (as defined in the table setup)?

It is also NOT a good idea to use a reserved word for the field name. Date
is a reserved word.

 

[Mark Carlyle via AccessMonster.com]

Trustacctnumber/trustalias is a number

do I have it correct or wrong?


As for Date... I know ... I have issues with that in another program. I am
going to have to change it.

The way you have this written, ADay, AMonth, and AYear are values from the
query's fields. You need to define fields in the Transactions table that the
value from these fields will limit. Is [Date] a field in Transactions? If
so, it may be easier to use:

"[Date]<=#" & DateSerial([AYear], [AMonth], [ADay]) & "# And
[TrustAcctNumber]=" & [TrustAlias])

You also have two "" after [TrustAlias]. Is [TrustAlias] a number or string?
Is [TrustAcctNumber] a number or string (as defined in the table setup)?

It is also NOT a good idea to use a reserved word for the field name. Date
is a reserved word.

I am trying to work with Dsum in a query... but I get a multiplication of
the

[quoted text clipped - 16 lines]

TIA

 

[Mark Carlyle via AccessMonster.com]

I made the changes you suggested... I get the same problem as before... here
is the output I am getting

trustalias Date Credit Debit total Daily Balance IDalias
103444827 7/15/2005 $8,745.43 $0.00 $8,745.43 $52472.58 222
103444827 7/11/2005 $101.99 $0.00 $101.99 $509.95 223
103444827 7/8/2005 $0.00 $34.00 ($34.00) -$136.00 84
103444827 7/7/2005 $0.00 $34.00 ($34.00) -$102.00 85
103444827 7/1/2005 $0.00 $34.00 ($34.00) -$68.00 86
103444827 6/24/2005 $0.01 $0.00 $0.01 $0.01 87

what total does is just give the difference fo credit - debit to correc the
number to a negative if needed.

trustalias is a number (defined as such)
IDalias is a key
the rest are setup as currency

TIA

Trustacctnumber/trustalias is a number

do I have it correct or wrong?

As for Date... I know ... I have issues with that in another program. I am
going to have to change it.

The way you have this written, ADay, AMonth, and AYear are values from the
query's fields. You need to define fields in the Transactions table that the

[quoted text clipped - 15 lines]

 

[Wayne Morgan]

With it being a number, it should be as I have it in the example.

 

[Wayne Morgan]

Is Total a calculated field in the query (i.e. [Credit]-[Debit]) or a field
in the underlying table? It should just be a calculated field in the query.
However, the DSum is looking for a field from the table. To make this work,
you may need to run 2 queries. The first one would be everything you have
except the DSum. The 2nd query would include all fields from the first query
and a calculated field for the DSum. The DSum would then use the first
query, not the table (Transactions), as its source.