How to filter if date is null by using a check box

[sspreyer]

Hi all
I have query which is filter by form only have 2 fields to filter field one is client which I filter by combo2 on my form have add this in the expression builder in my query works fine problem that I have got now is I have another field I need to filter called date start but I would like it so if checkbox "filter null" on my form is ticked to only show records that have no date in the field "date start" but if un ticked only shows records that have a date in the date start field

Thanks in advance



Shane

[rpeare]

Other people may have a more elegant solution than this but in situations like this I have a column in the query driving my query results that's something like:

Show: iif([forms]![FORMNAME]![CHECKBOXNAME] = -1 AND isnull(DATEFIELD), 1, iif([forms]![FORMNAME]![CHECKBOXNAME] = 0 and not isnull(DATEFIELD), 1, 0))

and have a criteria on this field of 1

[sspreyer]

Thanks Rpeare
still having problems getting to work do I need to put this code

Show: iif([forms]![FORMNAME]![CHECKBOXNAME] = -1 AND isnull(DATEFIELD), 1, iif([forms]![FORMNAME]![CHECKBOXNAME] = 0 and not isnull(DATEFIELD), 1, 0))

in criteria of the date field

sorry if I'm being a bit slow

thanks a lot for your time

[rpeare]

No, you're creating a calculated field

The result of that formula should correctly identify the records you want to show.

You simply put 1 in the criteria after you verify it's giving you the results you want

[sspreyer]

Thanks Rpeare work great thank you ever so much for time

Shane