Report Control Source In Text to Count

[OlBill]

Good morning,

I have built a text box to count based on a criteria (sum the total that meet a criteria).

My data is numbers above 0.05 and below 0.05. I also have "BRL" as data.

The formula I am using is "=Count(IIf([LeadResult]>"0.05",True))". It is giving me the count for the above 0.05, which is what I need, but it is also counting the BRL, which I need to exclude. Just count the >0.05.

How do I count just the >0.05 and exclude the "BRL"?

Thank you.

 

[Gasman]

AND LeadResult <> "BRL" ?

 

[Pat Hartman]

Count() doesn't work the way you think it does. Count(something) counts the number of not null values in the entire domain. Count(*) counts all records in the domain. To do what you want, you need to use the Sum() function.

=Sum(IIf([LeadResult] > .05,1,0))

The IIf() returns 1 or 0 and the Sum "counts" the 1's
Also, numbers are not enclosed in quotes.

 

[OlBill]

AND LeadResult <> "BRL" ?

Thank you.

I tried this and it still counted the the BRLs.

=Count(IIf([LeadResult]>"0.05",True) And ([LeadResult]<>"BRL"))

 

[OlBill]

Count() doesn't work the way you think it does. Count(something) counts the number of not null values in the entire domain. Count(*) counts all records in the domain. To do what you want, you need to use the Sum() function.

=Sum(IIf([LeadResult] > .05,1,0))

The IIf() returns 1 or 0 and the Sum "counts" the 1's
Also, numbers are not enclosed in quotes.

Thanks Pat. I got this "Data Type Mismatch in criteria expression"

 

[Gasman]

No
The criteria has to be all in the first part of the IIF?

@Pat Hartman O/P has a text field to hold those numbers, hence the complication with BRL as that must be > "0.05"

@OlBill You could perhaps use Val() to make it a number. BRL would then produce 0 ?

 

[Pat Hartman]

Is [LeadResult] a string? If so, change it back to "0.05"

But,WHY is it a string instead of a number?

 

[OlBill]

No
The criteria has to be all in the first part of the IIF?

@Pat Hartman O/P has a text field to hold those numbers, hence the complication with BRL as that must be > "0.05"

@OlBill You could perhaps use Val() to make it a number. BRL would then produce 0 ?

Thanks @Gasman, I have no idea where or how to do that.

 

[Gasman]

 "=Count(IIf(Val(LeadResult)>0.05,True))"

 

[OlBill]

Is [LeadResult] a string? If so, change it back to "0.05"

But,WHY is it a string instead of a number?

If by string you mean the field in that table, because of the BRL.

 

[OlBill]

"=Count(IIf(Val(LeadResult)>0.05,True))"

Data Type mismatch in criteria expression

 

[Pat Hartman]

LOOK at the definition in the table. if it is text, the value is a string. Gasman seems to already know this so just add the quotes back as I suggested.

 

[Pat Hartman]

You CANNOT use COUNT to do this. I explained why.

=Sum(IIf([LeadResult] > "0.05",1,0))

=Count(IIf([LeadResult]>"0.05",True, Null)) -- might work because you are forcing a not null value or a null value and only non null values are counted but no one looking at this will understand what is going on without really thinking about it. The Sum() is much more obvious in what it is doing.

 

[OlBill]

LOOK at the definition in the table. if it is text, the value is a string. Gasman seems to already know this so just add the quotes back as I suggested.

It is text.

Did it with the quotes,

=Sum(IIf([LeadResult]>"0.05",1,0))


It is counting the BRLs.

 

[Pat Hartman]

Great but the rest of the world has no idea what BRLs are

 

[OlBill]

Great but the rest of the world has no idea what BRLs are

Below Readable Limit. I'm trying to count just what is above 0.05. BRL is below 0.05.

 

[OlBill]

I am trying to get the total of the red boxes.

 

[Minty]

You will have to use

VAL([LeadResult]) > 0.05

Because you are comparing a string thinking it's a number!
In the immediate window:

? "BRL" > "0.05"
True

Access doesn't know that BRL as a string has no value, unless you force it to a number type.

 

[OlBill]

You will have to use

VAL([LeadResult]) > 0.05

Because you are comparing a string thinking it's a number!
In the immediate window:

? "BRL" > "0.05"
True

Access doesn't know that BRL as a string has no value, unless you force it to a number type.

Thank you Minty. I don't know what the immediate window is.

 

[OlBill]

=Val([LeadResult])>0.05

Gives me "#Type!"