Re: [MS_AccessPros] Getting "Balance" query

 

Khalid-

David is right!  I know we fixed this problem in earlier queries, but I hadn't remembered it.  Your query needs to look like:

SELECT TransactionID, TransactionDate, ClientCIN, ClientName, Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, Expenses, AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] = '" & [ClientCIN] & "'") AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] = '" & [ClientCIN] & "'") - DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] = '" & [ClientCIN] & "'")  AS Balance

FROM MiscAccount

WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

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)

On Jan 13, 2014, at 4:50 PM, Dave Williams <davewi11@yahoo.co.uk> wrote:



Khalid,

 

I only use Access for my own purposes, and am no expert, but I investigated your latest query as it uses Dcount, which I have never used but wondered if it would be useful to me.

 

Your query seems to be intended to display all the transactions from a particular client, and your last three fields as totals for all that client's transactions.

 

After much experimentation with quotes and brackets, I conclude that Dsum does not recognise [Enter Client ID] as a parameter to be entered. Access Help says that Dsum only recognises existing fields.

 

I tried splitting your query into two, with Query1 displaying the fields without your final three, and Query2 using Dsum applied to fields of Query1, but again I got an error saying [Enter Client ID] can not be found, even though this term did not exist in Query2.

 

All I was able to do is use Query1 to display the individual records, and make Query2 use Sum of fields in Query1 to give the answers you require. I also added all the other fields and put "Last" instead of "Group By" in the Design Grid - this gave one transaction, but it wasn't the last row in MiscAccount, it was the last one I had edited. Sorting Query1 did not change anything.

 

Here are my SQL's:

 

Query1:
SELECT MiscAccount.TransactionID, MiscAccount.TransactionDate, MiscAccount.ClientCIN, MiscAccount.ClientName, MiscAccount.Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, MiscAccount.Expenses, MiscAccount.AmountReceived
FROM MiscAccount
WHERE (MiscAccount.ClientCIN) Like [Enter Client ID];

Query2:
SELECT Query1.ClientCIN, Query1.ClientName, Last(Query1.TransactionID) AS LastOfTransactionID, Last(Query1.TransactionDate) AS LastOfTransactionDate, Last(Query1.Details) AS LastOfDetails, Last(Query1.ConsignmentNo) AS LastOfConsignmentNo, Last(Query1.Cartons) AS LastOfCartons, Last(Query1.Weight) AS LastOfWeight, Last(Query1.Expenses) AS LastOfExpenses, Last(Query1.AmountReceived) AS LastOfAmountReceived, Sum(Query1.Expenses) AS SumOfExpenses, Sum(Query1.AmountReceived) AS SumOfAmountReceived, Sum([AmountReceived]-[Expenses]) AS Balance
FROM Query1
GROUP BY Query1.ClientCIN, Query1.ClientName;

Dave W

 

----- Original Message -----

From: Khalid Tanweer

To: MS_Access_Professionals@yahoogroups.com

Sent: Monday, January 13, 2014 7:33 AM

Subject: Re: [MS_AccessPros] Getting "Balance" query

 

John,

I'm still floating in this query

SELECT TransactionID, TransactionDate, ClientCIN, ClientName, Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, Expenses, AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'") - DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID] & " AND [ClientCIN] LIKE '" & [Enter Client ID] & "'")  AS Balance

FROM MiscAccount

WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

I am getting values for Expenses and AmountReceived, but getting #Error for TotCash, TotExpense and Balance

Khalid

On Saturday, January 11, 2014 10:42 PM, John Viescas <JohnV@msn.com> wrote:

 

Khalid-

That clearly is NOT the datasheet view of the SQL you posted.  Your SQL asks for columns TransactionID, TransactionDate, ClientCIN, ClientName, Details, ConsignmentNo, Cartons, Weight, Expenses, AmountReceived, TotCash, TotExpense, and Balance.  Are you getting any values for Expenses and AmountReceived?  You do need to add a filter on ClientCIN to all the DSUM expressions.

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)

On Jan 11, 2014, at 6:24 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

That is OK now.

Now we are left with 2nd query (ClientsBalanceSheetMiscAccountQuery) which is upto now as follows:

SELECT MiscAccount.TransactionID, MiscAccount.TransactionDate, MiscAccount.ClientCIN, MiscAccount.ClientName, MiscAccount.Details, "" AS ConsignmentNo, "" AS Cartons, "" AS Weight, MiscAccount.Expenses, MiscAccount.AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID])-DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS Balance

FROM MiscAccount

WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

Showing datasheet view as:

TransactionID Expenses AmountReceived TotCash TotExpense Balance

8 4607 Null #Error #Error #Error

9 Null 4300 #Error #Error #Error

The final thing is that we need a final query for a report, are we working on the right track for our target?

Khalid

On Monday, January 6, 2014 1:57 PM, John Viescas <JohnV@msn.com> wrote:

 

Khalid-

Just because you added that code doesn't mean you don't have null values in TransactionID.  What do you see when you open ManagersAccounts in Datasheet view and sort on TransactionID?  If you see any "blank" fields, that's your problem.

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)

On Jan 6, 2014, at 8:33 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

I have tried this code like this:

Private Sub TransactionDate_GotFocus()

    ' If on a new record,

    If Me.NewRecord Then

        ' Assign the "next" value to the ID

        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1

    End If

End Sub

Query is the first one which you are saying should work unless there are null values in TransactionID field.

Now with this code i dont have null value in TransactionID field, BUT still not getting value in "balance"

Khalid

On Monday, January 6, 2014 11:37 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

 

John,

First of all i should mention, that query was other database "PCTL" and now i working on other database "PCTL Accounts".

Yes there would be a null value if it is the first entry on this form that was the reason i mentioned about the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' If on a new record,

    If Me.NewRecord Then

        ' Assign the "next" value to the ID

        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1

    End If

The reason why i included this code is with reference to my previous thread in which you suggested this code on 2 Jan see below:

John Viescas

To MS_Access_Professionals@yahoogroups.com

Jan 2 at 5:45 PM

Khalid-

First of all, there is a function called IsNothing, but it requires an object as an argument, not a field or control.

As the article implies, you first have to change your AutoNumber field to Long Integer, but I recommend you make a backup copy of the table first.

Your code needs to go in the BeforeUpdate event of the form.  It should look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' If on a new record,

    If Me.NewRecord Then

        ' Assign the "next" value to the ID

        Me.TransactionID = NZ(DMax("TransactionID", "Transactions"), 0) + 1

    End If

End Sub

… where "Transactions" is the name of your table.

Hope you can understand me now.

Khalid

On Monday, January 6, 2014 2:15 AM, John Viescas <JohnV@msn.com> wrote:

 

Khalid-

The first query should work unless there are Null values in the TransactionID field in any of the rows.

And your code is wrong.  It should be:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' If on a new record,

    If Me.NewRecord Then

        ' Assign the "next" value to the ID

        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1

    End If

    '-------------------------

    Dim msg As String

    msg = msg & "Do you want to Save this record?"

    If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbNo Then

        Cancel = True

        Me.Undo

    End If

End Sub

I have no clue why you included the code - it has nothing to do with the query.

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)

On Jan 5, 2014, at 5:44 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,

Sometime in the near past i had a problem to get balance on a continuous form where i had "TransactionID" field, key, Auto Number, which was resolved by John thanks to him again.

Which was:

SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & [TransactionID])-DSum("Expenses","ManagersAccounts","TransactionID <=" & [TransactionID]) AS Balance

FROM ManagersAccounts

ORDER BY ManagersAccounts.TransactionID;

But now i have an other form where TransactionID field is key, Number, Long Integer,  and forms before update is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    ' If on a new record,

    If Me.NewRecord Then

        ' Assign the "next" value to the ID

        Me.TransactionID = Nz(DMax("TransactionID", "ManagersAccounts"), 0) + 1

    End If

    '-------------------------

    Dim msg As String

    msg = msg & "Do you want to Save this record?"

    If MsgBox(msg, vbYesNo, "PCTL Accounts - Confirmation!") = vbYes Then

        DoCmd.Save

    Else

        Me.Undo

    End If

End Sub

While using the above query i get error message:

Syntex error (missing operator) in expression 'TransactionID <='.

this error message has to be clicked two times on OK button.

I tried this modification in the query and i do not get error message but output is not correct in balance i get the same figure on each row which was on first row balance:

SELECT ManagersAccounts.TransactionID, ManagersAccounts.TransactionDate, ManagersAccounts.ManagerID, ManagersAccounts.ManagerName, ManagersAccounts.Details, ManagersAccounts.BasicAmount, ManagersAccounts.NameOfCurrency, ManagersAccounts.ConversionRate, ManagersAccounts.TransactionMode, ManagersAccounts.AmountReceived, ManagersAccounts.Expenses, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotCash, DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS TotExpense, DSum("AmountReceived","ManagersAccounts","TransactionID <=" & "TransactionID")-DSum("Expenses","ManagersAccounts","TransactionID <=" & "TransactionID") AS Balance

FROM ManagersAccounts

ORDER BY ManagersAccounts.TransactionID;

Need help how to modify it.

Khalid

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (11)

Recent Activity:

• New Members 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___