isnull and convert to 0 in formula

[John1Chr]

Hi all,

Is there a way to make this formula work and convert to 0:

{@ALL COSTS FEDERAL}) - {@CE Local Costs}) if either isnull?

 

[jdemmi]

do you mean like this?

(if isnull{@ALL COSTS FEDERAL}) then 0 else {@ALL COSTS FEDERAL}) - (if isnull {@CE Local Costs}) then 0 else {@CE Local Costs})

-- Jason
"It's Just Ones and Zeros

 

[John1Chr]

jdemmi,

I'm getting the keyword then is missing error.

 

[jdemmi]

in my haste to replace my test formula names with yoursI fat fingered it...

(if isnull({@ALL COSTS FEDERAL}) then 0 else {@ALL COSTS FEDERAL}) - (if isnull({@CE Local Costs}) then 0 else {@CE Local Costs})



-- Jason
"It's Just Ones and Zeros

 

[John1Chr]

I'm a little dumbfounded why I don't see 0 when I do this formula.

 

[jdemmi]

maybe I misunderstood your request. the formula I posted checks each of your formulas for nulls substitues a 0 in it's place if null is found. it then subtracts the 2 values from another.

examples

4 - 2 = 2
1 - 0 = 1
" " - 1 = - 1
4 - " " = 4
" " - " " = 0

is this not what you wanted?



-- Jason
"It's Just Ones and Zeros

 

[John1Chr]

no jdemmi,

It was what I wanted. I got a different situation that's causing a malfunction we have a field that I'm using this formula...

{PROJECT.RPTG Month #} = tonumber(totext(Year(currentdate),0,"")&totext(month(currentdate),"00",0,""))

and it's flopping because today the reporting month # should be 200905 not 200906. This will keep bombing on the first of the month...any suggestions?

 

[jdemmi]

i am not exactly sure what you're trying to do witht the above data formula but it should probably be in a new post.

that said...

take look at the lastfullmonth function (Specifies a range of Date values that includes all dates from the first to last day of the previous month)

-- Jason
"It's Just Ones and Zeros