Isnull() in Excel Query

[kwilliam10]

Hi all,

I am trying to query data from one worksheet of an Excel workbook. In doing so, I want to add to columns together. One of the columns has null values in it. In Access I would use NZ()...but this is not available in Excel. I've tried the following:

1) case when isnull(segmentdata.Corrections,0) = True then 0 else segmentdata.corrections end as 'Diff'

But I get "Didn't expect 'isnull' after the SELECT column list."

2) case when segmentdata.corrections is null then 0 else segmentdata.corrections end as 'Diff'

But I get "Didn't expect 'segmentdata' after the SELECT column list"

3) coalesce(segmentdata.corrections,0)

But I get "Undefined function 'coalesce' in expression.

4) isnull(segmentdata.corrections)

Which gives me "-1" for null values, and 0 for non-null values


Can someone please point me in the right direction? I really need to convert nulls to zeros and be able to add the columns together.

Thanks in advance!!

Keith W.

 

[BrianB]

Up to my version (2000) Excel worksheet cells do not have specific a null format. Usually is either Text or Numeric depending on the cell format. I don't think General format is the same as null.

So an empty cell can be either 0 (zero) or "" (null string).
Problems of addition etc. are usually due to trying to add text and numbers together.

The problem may be due to using data imported into Excel.
The usual method of overcoming it is to select the whole column and give it a number format. Before unselecting the column use Data/Text to Columns ... Finish to force the conversion.

Hope this helps.

 

[kwilliam10]

Brian,

Thanks for your reply. Unfortunately, this didn't really help me. The individual columns that the query pulls the data from are number format. If the equation were strictly within excel, the math would work fine. But the math within the query, is what isn't working. Once the data is within ms query...it doesn't appear to follow the same logic. Currently, I simply have the query bring the data back, and THEN have the column right next to the query data, do the math. I would LOVE to have the query itself do the math.

Thanks!!

Keith W.