BALANCE

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

JIMMIE WHITAKER

Is the below query posible in vb code only? It works great as is in a
query. I read that to use Dsum, a table or query is required. Is there
another way around that?

The maim part of interest starts at AS Expr1. The query is query 5 by the
way, and dsum is summing on expr1 giving a current total in a checking
account. I want to make an mde and hide code, and not use query grid if
possible. Thanks

SELECT Transactions1.TN, Transactions1.TransactionDate,
Transactions1.TransactionDescription, Transactions1.WithdrawalAmount,
Transactions1.WDCLR, Transactions1.DepositAmount, Transactions1.DEPCLR,
NZ([Transactions1]![DepositAmount])-NZ([Transactions1]![WithdrawalAmount])
AS Expr1, Format(DSum("[Expr1] ","QUERY5","[TN] <=" & [TN]),"Currency") AS
BAL
FROM Transactions1
ORDER BY Transactions1.TN;
 
Should be possible. You need to double up the existing quote marks. Everywhere
the original statement has " you need to put ""

strSQL = "SELECT Transactions1.TN, Transactions1.TransactionDate," & _
"Transactions1.TransactionDescription, Transactions1.WithdrawalAmount," & _
"Transactions1.WDCLR, Transactions1.DepositAmount, Transactions1.DEPCLR," & _
"NZ([Transactions1]![DepositAmount])-NZ([Transactions1]![WithdrawalAmount])" & _
"AS Expr1, " & _
"Format(DSum(""[Expr1]"",""QUERY5"",""[TN] <="" & [TN]),""Currency"") AS BAL"
& _
" FROM Transactions1" & _
" ORDER BY Transactions1.TN"
 
I figured it out, I need not use query...
I just put expr1 inside a dsum, but base it on table instead of query, the
temp expression is still there as Bal. Thanks.

Dim STRSQL
STRSQL = "SELECT Transactions1.TN, Transactions1.TransactionDate,
Transactions1.TransactionDescription, Transactions1.WithdrawalAmount,
Transactions1.WDCLR, Transactions1.DepositAmount, Transactions1.DEPCLR," _
& "
Format(DSum('NZ([Transactions1]![DepositAmount])-NZ([Transactions1]![Withdra
walAmount])','TRANSACTIONS1','[TN] <=' & [TN]),'Currency') AS BAL" _
& " FROM Transactions1;"
Me.RecordSource = STRSQL
DoCmd.GoToRecord , , acNewRec

John Spencer (MVP) said:
Should be possible. You need to double up the existing quote marks. Everywhere
the original statement has " you need to put ""

strSQL = "SELECT Transactions1.TN, Transactions1.TransactionDate," & _
"Transactions1.TransactionDescription, Transactions1.WithdrawalAmount," & _
"Transactions1.WDCLR, Transactions1.DepositAmount, Transactions1.DEPCLR," & _
Click to expand...
"NZ([Transactions1]![DepositAmount])-NZ([Transactions1]![WithdrawalAmount])"
& _
"AS Expr1, " & _
"Format(DSum(""[Expr1]"",""QUERY5"",""[TN] <="" & [TN]),""Currency"") AS BAL"
& _
" FROM Transactions1" & _
" ORDER BY Transactions1.TN"

JIMMIE said:
Is the below query posible in vb code only? It works great as is in a
query. I read that to use Dsum, a table or query is required. Is there
another way around that?

The maim part of interest starts at AS Expr1. The query is query 5 by the
way, and dsum is summing on expr1 giving a current total in a checking
account. I want to make an mde and hide code, and not use query grid if
possible. Thanks

SELECT Transactions1.TN, Transactions1.TransactionDate,
Transactions1.TransactionDescription, Transactions1.WithdrawalAmount,
Transactions1.WDCLR, Transactions1.DepositAmount, Transactions1.DEPCLR,
NZ([Transactions1]![DepositAmount])-NZ([Transactions1]![WithdrawalAmount])
AS Expr1, Format(DSum("[Expr1] ","QUERY5","[TN] <=" & [TN]),"Currency") AS
BAL
FROM Transactions1
ORDER BY Transactions1.TN;
Click to expand...
Click to expand...
 
Back
Top