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
(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
(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
>
>
>
>
>
>
>
>
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar