ACCESS FEED: RE: [MS_AccessPros] Balance not updating

Sabtu, 26 Oktober 2013

RE: [MS_AccessPros] Balance not updating

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
>
>
>
>
>
>
>
>
>
>
>