Record source

  • Thread starter Thread starter JIMMIE WHITAKER
  • Start date Start date
J

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;
 
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")
 
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.
James Bertino said:
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 said:
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;
Click to expand...
Click to expand...
 
Back
Top