Null or Empty field Problem 1

[SmokeEater]

I am using Crystal 9 Advanced and an Oracle 8.1.7.4 database with ODBC connection, CR Oracle ODBC Driver 4.10.

I am trying to get this formula to work

Local NumberVar x; //Declare x to be a Number variable
Local NumberVar y; //Declare y to be a Number variable

x:={ASSETLCF.UNITCOST};
y:={PARTSLCF.AVERAGECOST};

if x <= 0 then x:=0;
if y <= 0 then y:=0;

If IsNull({ASSETLCF.UNITCOST}) then
x:=0
else
x:={ASSETLCF.UNITCOST};

If IsNull({PARTSLCF.AVERAGECOST}) Or Trim(CStr({PARTSLCF.AVERAGECOST})) = " " Then
y:=0
else
y:={PARTSLCF.AVERAGECOST};

if x>y then
x
else
y;

My problem is that occasionaly AVERAGECOST is blank and I then get no result from this code.

 

[Turkbear]

Hi,
Seems like too many tests, just try:

Local NumberVar x; //Declare x to be a Number variable
Local NumberVar y; //Declare y to be a Number variable

x:={ASSETLCF.UNITCOST};
y:={PARTSLCF.AVERAGECOST};

If (x < 0 or IsNull(x) )
then
x := 0;
If (y < 0 or IsNull(y) )
then
y := 0;
If x > y then
x 
else
If x <= y then
y;

If the 2 database fields are Number fields,. that should be enough...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SmokeEater]

OK, I took what you posted and pasted it in place of my code, checked it and got the error, "A Field Is Required Here", for the x and y variables. I replaced the x an y with field names and the code still does not work.

 

[lbass]

Try removing the space between the quotes in the null check for average cost.

-LB

 

[SmokeEater]

Tried that. No success!! I have also downloaded and installed the latest hotfix, in case it was a program problem. Also I've tried

Instr(Trim(Cstr({PARTSLCF.AVERAGECOST})),"")

and

Instr(Trim(Cstr({PARTSLCF.AVERAGECOST}))," ")

in an attempt to find some way to identify if the field is Null, Empty or Blank

 

[Turkbear]

Hi,
On what line was the error? Anyway, was this what you tried:

Local NumberVar x; //Declare x to be a Number variable
Local NumberVar y; //Declare y to be a Number variable

If (
{ASSETLCF.UNITCOST} < 0 or IsNull({ASSETLCF.UNITCOST})
 )
then
x := 0;
If (
{PARTSLCF.AVERAGECOST}< 0 or IsNull(PARTSLCF.AVERAGECOST})
 )
then
y := 0;

If x > y then
x 
else
If x <= y then
y;

What error did this cause?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SmokeEater]

This is what the code looked like when I was done. The error was

A Field Is Required Here

until the x and y were replaced with field names.

Local NumberVar x; //Declare x to be a Number variable
Local NumberVar y; //Declare y to be a Number variable

x:={ASSETLCF.UNITCOST};
y:={PARTSLCF.AVERAGECOST};

If (x < 0 or IsNull({ASSETLCF.UNITCOST}) )
then
x := 0;
If (y < 0 or IsNull({PARTSLCF.AVERAGECOST}) )
then
y := 0;
If x > y then
x 
else
If x <= y then
y;

 

[Turkbear]

Hi,
Sorry, still not sure what the error applied to:
In the last version I posted, when you tested/ran it, where did the
'A Field Is Required Here'
message point?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Try:

Local NumberVar x; //Declare x to be a Number variable
Local NumberVar y; //Declare y to be a Number variable

If IsNull({ASSETLCF.UNITCOST}) or
{ASSETLCF.UNITCOST} < 0 then
x := 0 else
x := {ASSETLCF.UNITCOST};
If IsNull({PARTSLCF.AVERAGECOST}) or
{PARTSLCF.AVERAGECOST} < 0 then
y := 0 else
y := {PARTSLCF.AVERAGECOST};
maximum([x,y]);

-LB

 

[SmokeEater]

Thanks lbass, that is the fix.

Turkbear, I appreciate all your efforts.