how to filter on a column that sometimes is null? (1 Viewer)

[wcoast]

Hi!

I want to be able to filter a form based on a dropdown box, where one of the items can be null. (The field is of type short date).

the dropdown box is named "Fltr1".

The form is based on a query, and i have tried to express the filter in the query like this (sql syntax):

([Tablename].[Columnname]) Like "*" & [Forms]![frmMyForm]![Fltr1])

But this way, the records with this column being null are omitted...

I very much appreciate any help that leads me in the right direction

thanks

 

[neileg]

Use Nz() to convert your nulls to something else for the purposes of the filter

 

[wcoast]

and how do i use Nz()?

Thanks for the reply I am unfamiliar with the Nz() and how (and where) to use it?

thnx

 

[ShaneMan]

Hi!

I want to be able to filter a form based on a dropdown box, where one of the items can be null. (The field is of type short date).

the dropdown box is named "Fltr1".

The form is based on a query, and i have tried to express the filter in the query like this (sql syntax):

([Tablename].[Columnname]) Like "*" & [Forms]![frmMyForm]![Fltr1])

But this way, the records with this column being null are omitted...

I very much appreciate any help that leads me in the right direction

thanks

Hey wcoast,

Not positive on this but try:

IsNull & ([Tablename].[Columnname]) Like "*" & [Forms]![frmMyForm]![Fltr1])

Hope I followed what you were asking for correctly and that this helps,
Shane