Select a null value from a field with a VBscript Macro

[#1]

Hi,

I'm desperately trying to select null values from a field.
I need to make null selections of a field from a button.

Is it possible or not ?

That's the vbscript

Sub Test
ActiveDocument.Fields("Start_Date").Select "isnull()"
End Sub

Sub Test_two
set f = ActiveDocument.GetField ("Start_Date")
f.ParetoSelect "isnull(Start_Date)"
End Sub

Thanks in advance for your answers, so far so fast so good...

russianblue

[Oleg_Troyansky · Accepted solution]

I don't think you can select a null value - not from a macro, and not manually on the screen. You'd need to modify your script and replace nulls with some kind of value, like "N/A" or "MISSING" or something like that.

Oleg

[#3]

Thanks Oleg,

I already applyed the solution you suggested me in other reports I've made.

I just thought if there was a different solution using a macro instead of modifying the script...

rb

[#4]

Do your Null values show up in the Field Selection Boxes as blanks?

I just did a quick test with Inlined data (containing some nulls). In the macro, I used:

ActiveDocument.Fields("Value").Select ""

And it selected the Null (blank) value. I'm not sure if this will work for all situations.

[#5]

I tried to select null values using:
ActiveDocument.Fields("My Field").Select ""
but it doesn't work...

at least I had to modify the script using an "NA" value...

thanks

rb

[Anonymous]

The blank values from an inline load are not null values but blank values and will not respond to isnull() for example. If you try loading from Excel with blank cells you will get proper nulls.

If you do need null values and want to be able to interact with them you should look up NullAsValue in the F1-help in QlikView for null handling.

[#7]

I already took a look to "NullAsValue" statement and found that it's a dangerous solution... sometimes it can display unespected join results. As I said I'm just looking for an easy way to select in a Field null values with a macro. Thank you so much for your interest. rb

[#8]

Try thinking about it this way... QlikView data tables are not the same as, say, MSAccess tables. The QlikView data table is not a grid in which values are held, it is a collection of fields (columns) in which the values are associated with values in other fields (rows). Where there is no association, the grid DISPLAYED in a table object shows a null value. This is because the value does not exist, not that there is a value that is undefined.

I think what you are trying to do is to find something that is not there based on the assumption that QlikView has some way of knowing it is not there.

I have found that it is nearly always better to set a missing value as something known than to leave them as missing (as suggested by Oleg).