Dsum Oops Below simplified query instead of debit I used amount field

[rudolfelizabeth]

Id Linenumber Number Transdate Action Amount Balance Reference AliasBalBeginbaltotal3 AliasBalcarryforwardruntotal
2 0 100 31-03-2009 Deposit 50.00 0.00 No 657 75 110
3 0 100 31-03-2009 Withdrawal -15.00 0.00 Paymant electricity 75 110
5 0 100 28-04-2009 Deposit 25.00 0.00 75 135
8 0 100 31-08-2009 Withdrawal -50.00 0.00 75 85


AliasBalcarryforwardruntotal: DSum("Amount","transactions2","number=" & [number] & " and [Transdate]<=#" & Format([Transdate],"mm-dd-yyyy") & "#")

the first row should be 75 + 50 = 125
the second 125-15=110
the query gives for both fields with the same transaction date the same value the value of 110

Now I need also to the sum of positive amounts in the amounts field I used the following code

Sumofdeposits: DSum("Amount","transactions2","[amount]>0" and number=" & [number] & " and [Transdate]<=#" & Format([Transdate],"mm-dd-yyyy") & "#")

I dont get the correct values

 

[PHV]

AliasBalcarryforwardruntotal: DSum("Amount","transactions2","number=" & [number] & " and ([Transdate]<#" & Format([Transdate],"yyyy-mm-dd") & "# OR ([Transdate]=#" & Format([Transdate],"yyyy-mm-dd") & "# AND Id<" & [Id])))

Sumofdeposits: DSum("Amount","transactions2","amount>0 and number=" & [number] & " and ([Transdate]<#" & Format([Transdate],"yyyy-mm-dd") & "# OR ([Transdate]=#" & Format([Transdate],"yyyy-mm-dd") & "# AND Id<" & [Id])))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rudolfelizabeth]

Dear PHV


I tried your code, I copied and pasted it in the query.
It produce an error in the field.

I forgot to inform this must be for access 2003

I also need to format the field to currency.

 

[PHV]

It produce an error
Which error message ?
What is your SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rudolfelizabeth]

Id Linenumber Number Transdate Action Amount Balance Reference AliasBalBeginbaltotal3 AliasBalcarryforwardruntotal Sumofdeposits Sumofwithdrawals AliasBalcarryforwardruntotal2 Sumofdeposits2 Balancofdeposits_witdrawals
2 0 100 31-03-2009 Deposit 50.00 0.00 No 657 75.00 110.00 75.00 -65.00 #Error #Error 75.0075.00-65.00
3 0 100 31-03-2009 Withdrawal -15.00 0.00 Paymant electricity 75.00 110.00 75.00 -65.00 #Error #Error 75.0075.00-65.00
5 0 100 28-04-2009 Deposit 25.00 0.00 75.00 135.00 25.00 -50.00 #Error #Error 75.0025.00-50.00
8 0 100 31-08-2009 Withdrawal -50.00 0.00 75.00 85.00 -50.00 #Error #Error 75.00-50.

YOUR CODE HAD TO MANY PARENTHESES SO I took them off
here are codes

first code:
AliasBalBeginbaltotal3: Format(DSum("Amount","transactions2","Number=" & [Clientnumber] & " and [Transdate]<=#" & Format([Yourdate],"mm-dd-yyyy") & "#"),"fixed")

this is correct

second code:
AliasBalcarryforwardruntotal: Format(DSum("Amount","transactions2","number=" & [number] & " and [Transdate]<=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed")

this code doesnot produce the correct results.
third code
Sumofdeposits: Format(DSum("Amount","transactions2","[amount]>0 and number=" & [number] & " and [Transdate]>=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed")
no correct results
fourth code:
Sumofwithdrawals: Format(DSum("Amount","transactions2","[amount]<0 and number=" & [number] & " and [Transdate]>=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed")
not the correct results

your code:

AliasBalcarryforwardruntotal2: DSum("Amount","transactions2","number=" & [number] & " and ([Transdate]>#" & Format([Transdate],"yyyy-mm-dd") & "# OR ([Transdate]=#" & Format([Transdate],"yyyy-mm-dd") & "# AND Id<" & [Id])
error
my last code
Balancofdeposits_witdrawals: ([AliasBalbeginbaltotal3]+[Sumofdeposits]+[sumofwithdrawals])
no correct results

SQl code
SELECT Transactions2.Id, Transactions2.Linenumber, Transactions2.Number, Transactions2.Transdate, Transactions2.Action, Transactions2.Amount, Transactions2.Balance, Transactions2.Reference, Format(DSum("Amount","transactions2","Number=" & [Clientnumber] & " and [Transdate]<=#" & Format([Yourdate],"mm-dd-yyyy") & "#"),"fixed") AS AliasBalBeginbaltotal3, Format(DSum("Amount","transactions2","number=" & [number] & " and [Transdate]<=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed") AS AliasBalcarryforwardruntotal, Format(DSum("Amount","transactions2","[amount]>0 and number=" & [number] & " and [Transdate]>=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed") AS Sumofdeposits, Format(DSum("Amount","transactions2","[amount]<0 and number=" & [number] & " and [Transdate]>=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed") AS Sumofwithdrawals, DSum("Amount","transactions2","number=" & [number] & " and ([Transdate]>#" & Format([Transdate],"yyyy-mm-dd") & "# OR ([Transdate]=#" & Format([Transdate],"yyyy-mm-dd") & "# AND Id<" & [Id]) AS AliasBalcarryforwardruntotal2, DSum("Amount","transactions2","amount>0 and number=" & [number] & " and ([Transdate]<#" & Format([Transdate],"yyyy-mm-dd") & "# OR ([Transdate]=#" & Format([Transdate],"yyyy-mm-dd") & "# AND Id<" & [Id]) AS Sumofdeposits2, ([AliasBalbeginbaltotal3]+[Sumofdeposits]+[sumofwithdrawals]) AS Balancofdeposits_witdrawals
FROM Transactions2
GROUP BY Transactions2.Id, Transactions2.Linenumber, Transactions2.Number, Transactions2.Transdate, Transactions2.Action, Transactions2.Amount, Transactions2.Balance, Transactions2.Reference
HAVING (((Transactions2.Number)=[Clientnumber]) AND ((Transactions2.Transdate)>=[Yourdate]))
ORDER BY Transactions2.Transdate;

Hope you can help me out.