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" ?

 

[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 ?

 

[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

 

[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.

 

[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!"

 

[Minty]

=Val([LeadResult])>0.05

Gives me "#Type!"

In the VBA editor press Ctrl + G to bring it up. Very handy for testing things. The ? is used to "Print" the result.

You need to use the whole statement, not just a bit of it.

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

 

[Gasman]

Data Type mismatch in criteria expression

Not sure why that would be.
I would also have used

=Count(IIf(Val(LeadResult)>0.05,1,0)

Could also use Sum() with that criteria

 

[Minty]

I have run across a similar problem dealing with Mobile data plans.
1Gb, 5Gb, 10Gb, Unlimited

It's a pain, so you deal with it by using a lookup to change unlimited to 100 - Then some bright spark starts selling 100Gb data plans...

 

[OlBill]

Go back to my original expression but fix the underlying problem which is the table definition. Change the field to be numeric so that it will compare properly.

It looks like you have a text value in the mix. What is BRL? You cannot put a text value in a field that otherwise holds numbers. You need to enter a numeric value.

When you define numbers as text the numbers work like they are text rather than numbers. The fact that you have centered the numbers confuses the issue. Numbers should ALWAYS be aligned at the decimal point. Set the display format to accommodate the largest decimal value.

The values in the table are aligned left because that is the way text is stored. The compare is done character by character left to right. Since you also seem to have values greater than zero, you will have bizzare sorting and compare results. For example 10.2 is going to be < 2.1 when you use a text compare.

Using Val() may work but the best solution is to fix the underlying problem which is allowing text and numbers in the same column and expecting the numbers to work correctly.

Thanks Pat. "BRL" is a result, just like 0.012 or 10.2. I understand what you are saying, but I can't choose the reporting criteria, I am at the mercy of the system. My reporting has to match the values sent by other people.

I definitely have a text value in the mix.

 

[Gasman]

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.

No, just going by the fact that the field had to hold BRL and that he was using "0.05" already.