ACCESS FEED: RE: [MS_AccessPros] Balance not updating

Minggu, 27 Oktober 2013

RE: [MS_AccessPros] Balance not updating

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