Query Criteria Input Via Form

Question

I'm have a set of data that can be cut many different ways. I'm trying to build a form that'll give me the ability to input criteria but also the ability to ignore criteria. I've got a form set up that pull in my input but a couple of elements are causing me trouble.

1. I've got each criteria set up as on the query design view as follows:

   Like (IIf(IsNull([Forms]![FRM Report Builder]![Combo7]),"*",[Forms]![FRM Report Builder]![Combo7]))

   For each of the 4 fields where i use criteria like this i want to be able to ignore it if its blank but use it if it is populated. Am i missing something really obvious in how i'm laying it out in design view. I get that there is an OR fields but i want it be something that say AND instead.

2. I want to do the same thing with a date range, i want the option to use dates if they've been input in the form but also ignore if missing. i got as far as the below but this doesn't work (and i have no idea why)

   Between ( Like (IIf(IsNull([Forms]![FRM Report Builder]![Text15]),"",[Forms]![FRM Report Builder]![Text15]))) And ( Like (IIf(IsNull([Forms]![FRM Report Builder].[Text17]),"",[Forms]![FRM Report Builder]![Text17])))

I'm working with some basic skills here so any answers need spoon feeding a bit. If its something i need to put outside of the 'Design View' please let me know.

Answer 1

You don't need the IIf() function. Use OR is Null in the criteria in this fashion;

 [Forms]![FRM Report Builder]![Text17] OR [Forms]![FRM Report Builder]![Text17] Is Null

This page gives an example of a very well thought out search form. http://allenbrowne.com/ser-62.html