459,377 Members | 1,655 Online
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

# Count Debits or Credits Amounts

 P: 36 Hello People I need to know if is possible to count Debits or Credits Amounts for example: IIF([field] = debits then 1 else 0 or IIF([field] = Credits the 1 else 0 To count the portion in debits or Credits. I need to type this IIF in a query to determine de calculation but i dont know how is the correct formatt for this.. Thanks every one Feb 25 '08 #1
8 Replies

 Expert 100+ P: 108 As taken from the MSDN: Visual Basic for Applications Reference IIf Function Returns one of two parts, depending on the evaluation of an expression. Syntax IIf(expr, truepart, falsepart) The IIf function syntax has these named arguments: Part Description expr Required. Expression you want to evaluate. truepart Required. Value or expression returned if expr is True. falsepart Required. Value or expression returned if expr is False. So you would be looking at something like: Expand|Select|Wrap|Line Numbers IIF([field] = "credit",1,0)   Hope this helps. - Minion - Feb 25 '08 #2

 P: 83 Hello... If you need to count the amount of Credits and Debits you have you would add these fields in your query: CountCredits:iif([Credits]="Credit", 1, 0) CountDebits:iif([Debits]="Debit", 1, 0) This will number the accounts that you have that say "Credit" in the Credits field with a 1 and "Debit" in the Debits field with a 1. This is a great tool for when you are creating a pivot report in excel so that it will give you the total count of Credit and Debit accounts. I would syggest making 2 fields, 1 for Credits and 1 for Debits instead of combining them. From your question, this is what I assumed you were asking, although, I was confused whether or not you wanted the amounts captured. Feb 25 '08 #3

 P: 36 Thanks, but the field have amounts. If the amount is in black color like debits then count 1 else 0. Otherwise if is in red like credits count 1 else 0. I only one field for amounts the formatt I used to determine credit o debits in the text box report is \$#,##0.00[Black];(\$#,##0.00)[Red];\0; Hello... If you need to count the amount of Credits and Debits you have you would add these fields in your query: CountCredits:iif([Credits]="Credit", 1, 0) CountDebits:iif([Debits]="Debit", 1, 0) This will number the accounts that you have that say "Credit" in the Credits field with a 1 and "Debit" in the Debits field with a 1. This is a great tool for when you are creating a pivot report in excel so that it will give you the total count of Credit and Debit accounts. I would syggest making 2 fields, 1 for Credits and 1 for Debits instead of combining them. From your question, this is what I assumed you were asking, although, I was confused whether or not you wanted the amounts captured. Feb 25 '08 #4

 P: 83 You can not determine debits/credits in a query based on colors. You would have to have another field in your table to display whether or not they are credit or debit or you can use specific amounts to interpret this. Feb 25 '08 #5

 Expert Mod 15k+ P: 31,768 What do you use to determine whether the record is a Credit or a Debit? Do you really want a count? Or are you actually looking for a Sum of the values? Mar 3 '08 #6

 P: 36 What do you use to determine whether the record is a Credit or a Debit? Do you really want a count? Or are you actually looking for a Sum of the values? Thanks Neo - I determine credits and debits with this formatt \$#,##0.00[Black];(\$#,##0.00)[Red];\0; - Yes I want to count. Mar 4 '08 #7

 Expert Mod 15k+ P: 31,768 ... - I determine credits and debits with this formatt \$#,##0.00[Black];(\$#,##0.00)[Red];\0; ... You mean negative numbers are credits and postive ones are debits yes? ... - Yes I want to count. OK I will see if I can get another look at this later. Running late and have a home to go to ;) Mar 4 '08 #8

 Expert Mod 15k+ P: 31,768 It seems that while I may have a home to go to, I have no viable means of transport just for now (motorbike broken down) so I'll have another look at this now. Assuming a table ([Table]) and a field ([Field]) in that table, then you want some SQL like : Expand|Select|Wrap|Line Numbers SELECT Count(IIf([Field]<0,Null,[Field])) AS NumDebits,        Count(IIf([Field]<0,[Field],Null)) AS NumCredits FROM [Table] This is very basic and counts across the whole table. If you need your counts to be done by particular grouping, then we need to know that before we can help further. Mar 4 '08 #9