Bi Query not displaying all data due to null entries

[Former Member]

HI everyone.

This is my first time here so I hope my question is in the right place and my format is correct.

So I have a problem with Bi Query and its isnull and isnotnull qualifications. My query works with around 500 to 1000 rows of data at a time. The main problem is one of the columns has both numbers ranging from 0-100 and blank cells. When I include this column as a result I want displayed it cuts off about half the rows from the results due to the blank space.

So my first fix was to include the column as a query as well and have it set to AND(col10 isnull OR col10 isnotnull). The results are the same, The half with blank spaces missing.

Second fix was to try and just pull null results and that came up with: No data found.

Third attempt is the same as the second but instead of null I tried pulling white space using the bi query special char {' '}   Result: no data Found

Am I missing something key here about the use of isnull and isnotnull or is there an extra step after these ones which I have not stumbled upon?

All my work so far has been based on what little resources I can find online and the Bi Query inbuilt help.

Anything I can try and mess with or test would be greatly appreciated.

Thanks in Advance

Flavius

[Loed]

Hi,

Is it KF? Did you suppress the zeros? You should choose DO NOT SUPPRESS in the ROWS/COLUMNS tab of your query..

Did I understand your scenario correctly? If not, please provide a SAMPLE screenshot of your report..

Regards,

Loed

[Former Member]

Hi thanks for the reply.

The main problem as i see it is that my query is not working as in I know column 10 has blank cells where nothing has been entered. And yet when i try and pull only those using col10 isnull I get 0 results.

My query is of the format

(col1=i) and (col2=>date) and (col3<=date)

This displays 100 results

When I include col10 as a result to be displayed and re run the same query I get 50 results.

And when I run (col1=i) and (col2=>date) and (col3<=date) and (col10isnull) with column 10 to be displayed as a result I get nothing.

I am very sorry I cannot be clearer with screenshots as I am on a restricted pc in terms of screenshots and access.

I really appreciate the advice so far.

If it can't be solved I'm gonna have to make do with running 2 sets of reports and jumping back and forth between them.

Thanks again for the help

Flavius

[Loed]

Hi,

If you can provide just sample data, not really a screenshot, it will help us solve your problem..

Regards,

Loed