Report Control Source In Text to Count

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

 

[Pat Hartman]

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.

 

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

 

[OlBill]

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

Ok, I'm in the VBA and see the immediate window. I put the < ?"BRL">"0.05"> in the immediate window? and the whole statement goes where?

 

[OlBill]

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

0.05 is the threshhold for positive results. Anything below 0.05, including BRL, is negative.

 

[OlBill]

I put <=Sum(IIf(Val([LeadResult])>0.05,1,0))> in the Control Source for the text box and

<?"BRL">"0.05"> in the Immediate Box in the VBA Editor.


"Data type mismatch in criteria expression"

 

[Gasman]

The BRL > 0.05 is just to show you in text that BRL is > 0.05 if you leave it all as text.

From my Immediate window, where I always test small stuff

? "BRL" > "0.05"
True
? VAL("BRL")
0
? VAL(0.05)
0.05
? Val("BRL") > Val("0.05")
False

So I have no idea what you are doing?
Best upload a sample, we are up to 30 posts on such a simple matter already.

Also do not put < & > at the end of code. Confusing as hell, to me at least. If you have to put them there, make them bold so we know they are NOT part of the code.

 

[OlBill]

Also do not put < & > at the end of code. Confusing as hell, to me at least. If you have to put them there, make them bold so we know they are NOT part of the code.

Got it.

The BRL > 0.05 is just to show you in text that BRL is > 0.05 if you leave it all as text.

From my Immediate window, where I always test small stuff

? "BRL" > "0.05"
True
? VAL("BRL")
0
? VAL(0.05)
0.05
? Val("BRL") > Val("0.05")
False

So I have no idea what you are doing?
Best upload a sample, we are up to 30 posts on such a simple matter already.

Also do not put < & > at the end of code. Confusing as hell, to me at least. If you have to put them there, make them bold so we know they are NOT part of the code.

Thanks @Gasman . I put your code in my immediate window. What do I do now?

 

[Gasman]

No, the immediate window is to test something and to see output from any Debug.Print
I only keyed that to show you what the results are for each test.

You need to get to the bottom of why the posted code using Val still produces a Type Mismatch.

 

[OlBill]

No, the immediate window is to test something and to see output from any Debug.Print
I only keyed that to show you what the results are for each test.

You need to get to the bottom of why the posted code using Val still produces a Type Mismatch.

Understood. Maybe I can post a sample tomorrow. The one I'm working with has sensitive data in it.
Thank you for your time.

No doubt @Pat Hartman is absolutely correct about the field. Maybe I can figure out a way to change BRL to a number.

 

[Gasman]

Well the Val() should have solved that problem, or so I would have thought.

Look here for some code to hide the real data.

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[Pat Hartman]

Have you tried this version yet?

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

It leaves the field as text and returns a 1 if the value is > the string and 0 if it is not. The 1's and 0's are summed.

In the future, you need to think long and hard about storing numeric and text values in the same field and then DON'T do it. Relational databases are NOT spreadsheets. All the data in a column is ALWAYS of the same data type unlike Excel where it can be mixed.

My suggestion is to use my earlier advice to FIX THE UNDERLYING PROBLEM and REMOVE the text so that only numbers are stored. Pick a number to represent "BRL" if you need a default.