Record source

[JIMMIE WHITAKER]

Below is a query8, and an sql statement that says the same thing, but is
behind the form only
as the record source. In the query qrid all is correct, here only, the
editor word wrapped.

How come the first query works correctly, but the second one when set as
forms record source
doesn't work. It shows data, but the wrong running total. First query all
is correct.

Works: when set as record source.

SELECT Transactions.TRANSID, Transactions.TransactionNumber,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount,
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount]) AS
Expr1, Format(DSum("[Expr1] ","QUERY8","[Transid] <=" &
[Transid]),"Currency") AS RBAL
FROM Transactions
WHERE (((Transactions.AccountID)=[Forms]![ACCOUNTS]![AccountID]))
ORDER BY Transactions.TRANSID;

Does not work: when set as record source.

SELECT Transactions.TRANSID, Transactions.TransactionNumber,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount, Format(DSum("
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])
","Transactions","[Transid] <=" & [Transid]),"Currency") AS RBAL
FROM Transactions
WHERE (((Transactions.AccountID)=[Forms]![ACCOUNTS]![AccountID]))
ORDER BY Transactions.TRANSID;

 

[James Bertino]

Jimmie I'm not familier with Query8 but I do see something in the second
querry that looks weird.
In the second querry you have --
**********
Format(DSum(">
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])>
","Transactions","[Transid] <=" & [Transid]),"Currency")
*********
But doesn't TRANSACTIONS work better without quotes
Format(DSum(">
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])>
","[Transactions],"[Transid] <=" & [Transid]),"Currency")

 

[JIMMIE WHITAKER]

The quotes are required in a dsum statement. The first is a saved query,
and the dlsum is working inside query 8. It works correctly, all running
balances are correct. The second query statement works, but balances are
incorrect. It's not a saved query, it's put behind form as record source.

Jimmie I'm not familier with Query8 but I do see something in the second
querry that looks weird.
In the second querry you have --
**********
Format(DSum(">
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])>
","Transactions","[Transid] <=" & [Transid]),"Currency")
*********
But doesn't TRANSACTIONS work better without quotes
Format(DSum(">
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])>
","[Transactions],"[Transid] <=" & [Transid]),"Currency")

Below is a query8, and an sql statement that says the same thing, but is
behind the form only
as the record source. In the query qrid all is correct, here only, the
editor word wrapped.

How come the first query works correctly, but the second one when set as
forms record source
doesn't work. It shows data, but the wrong running total. First query all
is correct.

Works: when set as record source.

SELECT Transactions.TRANSID, Transactions.TransactionNumber,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount,
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount]) AS
Expr1, Format(DSum("[Expr1] ","QUERY8","[Transid] <=" &
[Transid]),"Currency") AS RBAL
FROM Transactions
WHERE (((Transactions.AccountID)=[Forms]![ACCOUNTS]![AccountID]))
ORDER BY Transactions.TRANSID;

Does not work: when set as record source.

SELECT Transactions.TRANSID, Transactions.TransactionNumber,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount, Format(DSum("
NZ([Transactions]![DepositAmount])-NZ([Transactions]![WithdrawalAmount])
","Transactions","[Transid] <=" & [Transid]),"Currency") AS RBAL
FROM Transactions
WHERE (((Transactions.AccountID)=[Forms]![ACCOUNTS]![AccountID]))
ORDER BY Transactions.TRANSID;