Multiple Query Criteria Including including Date Range

[jabrjo]

Hello,



I am in the process of creating a Form that will allow users to enter criteria into text boxes/combo boxes and query data. I want the users to have the option of leaving the boxes blank so that all data is returned for null values. Right now I am using this expression to query data [Forms]![Form1]![cmbStore] and [Forms]![Form1]![cmbStore] is null. This seems to be working fine for all but the date range query. I was using the "Between" function for the date range, but cannot figure out how to apply "Is Null" to the query.

I posted a similar question earlier and a few AcccessForums.net members suggested I use the filter option for this type of form. I am not totally opposed to the filter method, however, I am not very good with VB and there appears to be a lot of code in that solution.

I attached an example of what I am trying to accomplish. Thank you for your help!

[June7]

Your options are:

1. manually modify query with static parameters

2. dynamic parameterized query (I NEVER use this)
SELECT * FROM table WHERE [date field] BETWEEN Nz([Forms]![formname]![textbox StartDate], #1/1/1900#) AND Nz([Forms]![formname]![textbox EndDate], #12/31/2900#)

3. code to build filter criteria http://www.allenbrowne.com/ser-62.html

[jabrjo]

Thanks June. Just curious - why don't you use the dynamic parameterized query? What issues can this cause?

Thanks

[June7]

As developer sometimes I open queries to view data and I don't want it filtered and don't want annoying parameter input popups because the associated form is closed.

[jabrjo]

Thanks June. This seemed to be working very well, but after a few queries i got an error message saying "Query too Complex" am i doing something wrong? Like i said, I have a multiple query boxes. The text and combo boxes have dynamic parameterized queries such as this: [Forms]![frmCompqry]![cmbID2] Or [Forms]![frmCompqry]![cmbID2] Is Null. I then added the date query you suggested. Like i said, it worked a few times but then i got an error saying it was too complex. Any suggestions?

I really appreciate your help.

[June7]

Would have to examine the forms and queries. If you want to provide db, follow instructions at bottom of my post.