ACCESS FEED: RE: [MS_AccessPros] Balance not updating

Khalid-

At the beginning of your thread, you said you were trying to use this query as the Record Source for a form. That should work just fine if you can open the query in Datasheet view and see the results. You cannot do an expression like this to create a column in a table. In fact, it's not a good idea to try to store a calculated value like this in a table. Use the query and a form to display the result.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Monday, October 28, 2013 6:20 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Balance not updating

John,

You pointed out on a very critical point, as i tried Duane's Sql before and did see only the table, where i do not have the fields TotCash and TotExpenses, In the Balance field of table results are blank yet.

But now again after reading your reply i again tried Duane's Sql and open the query in Database mode and viewed the results which are perfect, fields TotCash, TotExpenses, Balance are updated correctly.

So this means Duane's Sql is working correctly, but how should i manage that the results may display in table PettyCash ?

Khalid

On Monday, October 28, 2013 12:01 AM, John Viescas <JohnV@msn.com> wrote:

Khalid-

If there are no transactions between 131 and 158, then those are correct answers.

You say you tried Duane's SQL. The critical question is did you see any results in the fields TotCash and TotExpense?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Sunday, October 27, 2013 6:06 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Balance not updating

John,

i tried this expression replacing TransactionID from 132 - 156 individually and it gives the result 24727 which is the value of Balance field at TransactionID 131.

Replacing it with 157 it starts giving correct result for Balance, see below:

?DSum("AmountReceived","Petty Cash","TransactionID <= 132") - DSum("Expenses","Petty Cash","TransactionID <= 132")

24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 133") - DSum("Expenses","Petty Cash","TransactionID <= 133")

24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 134") - DSum("Expenses","Petty Cash","TransactionID <= 134")

24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 135") - DSum("Expenses","Petty Cash","TransactionID <= 135")

24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 136") - DSum("Expenses","Petty Cash","TransactionID <= 136")

24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 157") - DSum("Expenses","Petty Cash","TransactionID <= 157")

24627

?DSum("AmountReceived","Petty Cash","TransactionID <= 158") - DSum("Expenses","Petty Cash","TransactionID <= 158")

24607

I also did tried Duane's expression and it did not gave any result in Balance field.

Khalid

On Sunday, October 27, 2013 5:13 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Those look OK to me, but you will have to verify that the amounts calculated are correct. If these expressions work in the Immediate Window, there's no reason for the values to not be showing in your query.

As for the SQL, did you try the version that Duane posted below? It might have been a problem with the way the messages get line-wrapped.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Sunday, October 27, 2013 12:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Balance not updating

John,

I could not see the fields TotCash and TotExpenses, as i mentioned earlier that after putting your Sql i got an error message.

And while on the immediate window the results are as follows:

?DSum("AmountReceived","Petty Cash","TransactionID <= 200") - DSum("Expenses","Petty Cash","TransactionID <= 200")

5309

?DSum("AmountReceived","Petty Cash","TransactionID <= 184") - DSum("Expenses","Petty Cash","TransactionID <= 184")

5309

?DSum("AmountReceived","Petty Cash","TransactionID <= 132") - DSum("Expenses","Petty Cash","TransactionID <= 132")

24727

=> Against TransactionID: 131 actual Balance in the table is: 24727

?DSum("AmountReceived","Petty Cash","TransactionID <= 157") - DSum("Expenses","Petty Cash","TransactionID <= 157")

24627

=> Here Expenses field value is 100, so Balance should be 24627

Khalid

On Sunday, October 27, 2013 1:07 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

You could also go to the Immediate Window (CTRL+G) and type:

?DSum("AmountReceived","Petty Cash","TransactionID <= 200") - DSum("Expenses","Petty Cash","TransactionID <= 200")

.. and then press Enter. Note that I've substituted a constant TransactionID greater than 131 - you can try any number you like.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Sunday, October 27, 2013 7:50 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Balance not updating

Duane,

Still not filling field "Balance"

AmountReceived and Expenses fields are filling.

Khalid

On Sunday, October 27, 2013 2:20 AM, Duane Hookom <duanehookom@hotmail.com> wrote:

Khalid,
It would help if you came back with your SQL. I expect there was an issue with line wrapping in John's post. Try:

SELECT TransactionID, TransactionDate, Details,
AmountReceived, Expenses,
DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID])
As TotCash,
DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID])
As TotExpense,
DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID])-
DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID])
AS Balance
FROM [Petty Cash]
ORDER BY TransactionID;

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: khalidtanweerburrah@yahoo.com
> Date: Sat, 26 Oct 2013 12:58:58 -0700
> Subject: Re: [MS_AccessPros] Balance not updating
>
>
>
> John,
>
> After pasting your Sql, i gety a message:
> "Invalid SQL Statement; expected 'DELETE','INSERT',PROCEDURE',SELECT'
> or 'UPDATE'
>
> Khalid
>
>
> On Saturday, October 26, 2013 8:17 PM, John Viescas <JohnV@msn.com> wrote:
>
> Khalid-
>
> Try this:
>
> SELECT [Petty Cash].TransactionID, [Petty Cash].TransactionDate, [Petty
> Cash].Details, [Petty Cash].AmountReceived,
> [Petty Cash].Expenses, DSum("AmountReceived","Petty
> Cash","TransactionID <=" & [TransactionID]) As TotCash,
> DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) As
> TotExpense,
> DSum("AmountReceived","Petty Cash","TransactionID <=" &
> [TransactionID])-DSum("Expenses","Petty Cash","TransactionID <=" &
> [TransactionID]) AS Balance
> FROM [Petty Cash]
> ORDER BY TransactionID;
>
> Balance should simply be the difference between TotCash and
> TotExpense. Let me know if either of the two new fields don't show up.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
> Tanweer
> Sent: Saturday, October 26, 2013 5:07 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Balance not updating
>
>
>
>
> John,
>
> Only Balance field is missing the data. I did checked for entering in
> "AmountReceived" field and also in "Expenses" field both are giving
> data.
>
> Khalid
>
> On Saturday, October 26, 2013 6:17 PM, John Viescas
> <JohnV@msn.com<mailto:JohnV@msn.com>> wrote:
>
> Khalid-
>
> You might be getting a "blank" if either of the two DSum functions
> returns a Null value. Are there any missing values in either
> AmountReceived or Petty Cash fields?
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> From:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
> Tanweer
> Sent: Saturday, October 26, 2013 8:26 AM
> To:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Balance not updating
>
>
>
> Hi All,
> "Balan
> I have a form "Petty Cash Book" its Record Source is:
>
> SELECT [Petty Cash].TransactionID, [Petty Cash].TransactionDate, [Petty
> Cash].Details, [Petty Cash].AmountReceived, [Petty Cash].Expenses,
> DSum("AmountReceived","Petty Cash","TransactionID <=" &
> [TransactionID])-DSum("Expenses","Petty Cash","TransactionID <=" &
> [TransactionID]) AS Balance
> FROM [Petty Cash]
> ORDER BY TransactionID;
>
> Couple of days before i deleted some records manually, so now
> "TransactionID" field which is Auto Number is missing its sequence i.e.
> after TransactionID:131 i have TransactionID:157 and so on.
>
> Today viewing the report for Petty Cash Book/Table "Petty Cash" i
> observed that field "Balance" is blank after TransactionID:131.
>
> I can manually fill the Balance field, but i think this is not the
> right approach, so how could i manage it, and also for in future if i
> do manually delete any record this thing would again happen.
>
> What is the solution for this OR am i bound not to delete any record
> manually?
>
> Help for this would be highly appreciated with thanks.
>
> Khalid
>
>
>
>
>
>
>
>
>
>
>