Display formula works for one field but not the other 1

[cjbrown815]

Hello,
I have a manual Xtab report. I copied the first formula(Formula A
) below and pasted it in another field (Formula B) and it doesn’t work. I'm not getting red results for out of spec results. The only difference I can see is the results are 1000000 instead of just 100. I've tried everything to no avail. Please help




//Formula A
if {#LPC} = "TNTC" then crRed else
if {#LPC} = "NR" then crBlue else
if {#LPC} = "<1" then crGreen else
if {#LPC} in "1" to "4999" then crGreen else
if {#LPC} = ">150" then crRed else
if totext(val({#LPC}),"000") in "000" to "4999" then
crGreen else

if totext(val({#LPC}),"000") > "4999" then
crRed else
crNoColor



//Formula B
if {#ThermophilicCount} = "TNTC" then crRed else
if {#ThermophilicCount} = "NR" then crBlue else
if {#ThermophilicCount} = "<1" then crGreen else
if {#ThermophilicCount} in "1" to "999" then crGreen else
if {#ThermophilicCount} = ">150" then crRed else
if totext(val({#ThermophilicCount}),"000") in "000" to "999" then
crGreen else

if totext(val({#ThermophilicCount}),"000") > "999" then
crRed else
crNoColor


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Place the running total {#Thermophiliccount} in the detail section of the report and observe its value. There has to be a mismatch between the actual value the values you specify in the formula.

-LB

 

[cjbrown815]

Thanks LB,
There's just more figures / digits. The field that isnt working has 6 figures sometimes more. Is that the problem?



-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Please show samples of the results of {#ThermophilicCount} when placed in the detail section. Also explain the setup of the running total.

-LB

 

[cjbrown815]

Running total
Field to summarize = @ThermophilicCt
Type of Summary = maximum
Evaluate = For each Record
Reset = On Change of Group Group #3: SAMPLE.PRODCUT_LOT


Details result samples.

OK I'm getting the same data type (a string with a numeric value, 12000). But when I put a field that is working next to a field that is not working in the details section, the field that is working shows its results on each detail row, where the field that is not working only shows the result on the last row of the detail section. They are both under the same grouping though??



-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

Scratch that, the results are showing the same as the working fields. They are string fields showing numeric values

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

The only thing that really sticks out is that the two fields that are not working have substatually longer names ({#ThermophilicCount} and {#Therm Spore Count} vs. {#LPC}, {#SPC} or {#COLIFORM})

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

The results turn green only

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

I am asking you to show samples of actual values returned.

-LB

 

[cjbrown815]

[tt]
ThermophilicCount
1300
4000
7600
67000
120000
680
8600
[/tt]

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Try the following:

//Formula B
if {#ThermophilicCount} = "TNTC" then crRed else
if {#ThermophilicCount} = "NR" then crBlue else
if {#ThermophilicCount} = "<1" then crGreen else
if {#ThermophilicCount} = ">150" then crRed else
if val({#ThermophilicCount}) in 0 to 999 then
crGreen else
if val({#ThermophilicCount}) > 999 then
crRed else
crNoColor

Are there actual values "TNTC" and "<1" and ">150" ever returned? Are these somehow different than a value of "0" or "999" (which is greater than "150"?

-LB

 

[cjbrown815]

That worked!!!
The >150 value does not apply to this particular test, it is a requirement of other tests though.

TNTC = Too Numerous To Count
NR = No Result
<1 = 0, but we can't legally enter a 0 as it is assumed there are undetectable traces at all times.

as always,
Thank you


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931