By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,655 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
8 Replies


Minion
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
  1. IIF([field] = "credit",1,0)
  2.  
Hope this helps.

- Minion -
Feb 25 '08 #2

cori25
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

cori25
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

NeoPa
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

NeoPa
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

NeoPa
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
  1. SELECT Count(IIf([Field]<0,Null,[Field])) AS NumDebits,
  2.        Count(IIf([Field]<0,[Field],Null)) AS NumCredits
  3. 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

Post your reply

Sign in to post your reply or Sign up for a free account.