Count Debits or Credits Amounts

[ElTipo]

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

[Minion]

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:

IIF([field] = "credit",1,0)
 

Hope this helps.

- Minion -

[cori25]

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.

[ElTipo]

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.

[cori25]

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.

[NeoPa]

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?

[ElTipo]

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.

[NeoPa]

...
- 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 ;)

[NeoPa]

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 :

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.