query on blank or range

  • Thread starter Thread starter MaryMalone
  • Start date Start date
M

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!
 
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!
Click to expand...

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#)
Click to expand...
AND < NZ([Forms]![FrmProjectsChange]![ExpIL5end], #12/31/9999#)
 
Back
Top