query on blank or range

[MaryMalone]

I have a subform that is queried by combo boxes on the main form. THis works
fine. It was requested that I add a range filter for the date field in the
query. The criteria on the query originally looked as follows:

Like
(IIf(IsNull([Forms]![FrmProjectsChange]![ExpIl5]),"*",[Forms]![FrmProjectsChange]![ExpIL5])) Or Is Null

This worked fine for the single date. But if I try to add a range to the
formula it does not work. (see below) Suggestions?

Like (IIf(IsNull([Forms]![FrmProjectsChange]![ExpIl5]),"*", between
[Forms]![FrmProjectsChange]![ExpIL5] and
[Forms]![FrmProjectsChange]![ExpIL5end]))

Thanks!

 

[John W. Vinson/MVP]

I have a subform that is queried by combo boxes on the main form. THis works
fine. It was requested that I add a range filter for the date field in the
query. The criteria on the query originally looked as follows:

Like
(IIf(IsNull([Forms]![FrmProjectsChange]![ExpIl5]),"*",[Forms]![FrmProjectsChange]![ExpIL5])) Or Is Null

This worked fine for the single date. But if I try to add a range to the
formula it does not work. (see below) Suggestions?

Like (IIf(IsNull([Forms]![FrmProjectsChange]![ExpIl5]),"*", between
[Forms]![FrmProjectsChange]![ExpIL5] and
[Forms]![FrmProjectsChange]![ExpIL5end]))

Thanks!

That's because the LIKE operator is specifically for wildcard
searching of Text fields - not for date ranges.

Try

= NZ([Forms]![FrmProjectsChange]![ExpIL5], #1/1/100#)

AND < NZ([Forms]![FrmProjectsChange]![ExpIL5end], #12/31/9999#)