Balance query problem

[Mike Hughes]

When I run this query I’m getting “#Error” in Balance field.
What am I doing wrong?


SELECT Table1.TransID, Table1.Date, Table1.[Check #], Table1.[Issued To], Table1.Description, Table1.[Case ID], Table1.[NCP/PF], Table1.Child, Table1.State, Table1.Payment, Table1.[Deposit Date], Table1.[Deposit Amount], Table1.Cleared, DSum("Nz(Payment,0)-Nz([Deposit Amount],0)","Date <=

" & Format(Date,"\#mm-dd-yyyy\#") & "
AND (TransID <= " & TransID & " OR Date <>
" & Format(Date,"\#mm-dd-yyyy\#") & ")") AS Balance
FROM Table1
ORDER BY Table1.Date, IIf(Payment>[Deposit Amount],0,1), Table1.TransID;

Thanks Mike

 

[Guus2005]

You DSum statement is wrong. Use the Access help (F1) to see how to use it.

Dsum ( fieldname, tablename [, where clause])

the fieldname must be a valid name in that table and not the result of the Nz command.

You are using quote around the Nz command which is also not the way to use the Dsum statement, unless you have a field named "Nz(Payment,0)-Nz([Deposit Amount],0)" in you table with the name: "Date <=" & Format(Date,"\#mm-dd-yyyy\#") & " AND (TransID <= " & TransID & " OR Date <> " & Format(Date,"\#mm-dd-yyyy\#") & ")") "

If they are really the names you use i'd suggest you choose different ones...

Split the problem into smaller pieces and use queries to solve each problem. When each query produces the desired result, you can put them all together to create one.

HTH

 

[Mike Hughes]

Thanks, I'll give this a look today. I've been out due to a death in the family.

Mike

 

[Mike Hughes]

I believe I made the changes I thought you suggested.

This time query didn't run and got syntax error (missing operator) in query expression 0","Date <=

" & Format(Table1.Date,"\#mm-dd-yyyy\#") & "
AND (Table1.TransID <= " & Table1.TransID & " OR Table1.Date <>
" & Format(Table1.Date,"\#mm-dd-yyyy\#") & ")") AS Balance

FROM Table1

WHERE (((Table1.Date) Between [START DATE] And [END DATE]))

ORDER BY Table1.Date, Table1.[Check #];
================================================
Here is the query that I tried this time.


SELECT
Table1.Date,
Table1.[Check #],
Table1.[Issued To],
Table1.Description,
Table1.[Case ID],
Table1.[NCP/PF],
Table1.Child,
Table1.State,
Table1.Payment,
Table1.[Deposit Date],
Table1.[Deposit Amount],
Table1.Cleared,
Table1.Balance,

DSum(Table1.Payment,0)-Table1.[Deposit Amount],0","Date <=

" & Format(Table1.Date,"\#mm-dd-yyyy\#") & "
AND (Table1.TransID <= " & Table1.TransID & " OR Table1.Date <>
" & Format(Table1.Date,"\#mm-dd-yyyy\#") & ")") AS Balance
FROM Table1

WHERE (((Table1.Date) Between [START DATE] And [END DATE]))

ORDER BY Table1.Date, Table1.[Check #];

 

[Guus2005]

Again, your DSum function is not used correctly.

F1:
Dsum ( fieldname, tablename [, where clause])

used by you:
DSum(Table1.Payment,0)

It is safe to say that 0 is not a table name.

I believe you are trying to do something else. However the expression you use for the Balance field doesn't make any sense

DSum(Table1.Payment,0)-Table1.[Deposit Amount],0","Date <=
" & Format(Table1.Date,"\#mm-dd-yyyy\#") & "
AND (Table1.TransID <= " & Table1.TransID & " OR Table1.Date <>
" & Format(Table1.Date,"\#mm-dd-yyyy\#") & ")") AS Balance

The Dsum function can't be used this way. Use a Group By query instead

SELECT 
Table1.Date, 
Table1.[Check #], 
Table1.[Issued To], 
Table1.Description, 
Table1.[Case ID], 
Table1.[NCP/PF], 
Table1.Child, 
Table1.State, 
Table1.Payment, 
Table1.[Deposit Date], 
Table1.[Deposit Amount], 
Table1.Cleared,
Table1.Balance,

Table1.Payment-Table1.[Deposit Amount] AS ToBePaid

FROM Table1

WHERE (((Table1.Date) Between [START DATE] And [END DATE]))

ORDER BY Table1.Date, Table1.[Check #];

In the query design mode press the Sigma sign and change the Total row from "Group By" to "Sum" for the ToBePaid column.

you use the name "Balance" but there is a balance field in your table. To avoid circulair errors change the name to something else like "ToBePaid" or whatever.

HTH