Query from Form - If Date Range is Null bring all records

[bumblingidiot]

First from my user name I really just kind of stumble and bumble around access and various forums to get where I need to be with my database. But I think issue is a little too specific to just search for. Here is my issue:

What I want to query on is ProjectCode and DateFiled. I currently have a form (report type) where users will select the project and then choose the report type. This opens a second form with the project code already populated and the users enters a BeginningDate and EndingDate. If they leave the dates blank it should bring back all records.

My query is then set up with the parameter for the project code coming from the form ([Forms]![frmDateRange-SpecificProject]![txtProjectCode]). The date range is also pulled from the form (Between [Forms]![frmDateRange-SpecificProject]![txtStartDate] And [Forms]![frmDateRange-SpecificProject]![txtEndDate]). This all works fine.

However when I set up the additional criteria utilizing the fields such that with ([Forms]![frmDateRange-SpecificProject]![txtStartDate] or Is Null) and ([Forms]![frmDateRange-SpecificProject]![txtEndDate] or Is Null or Is Null) I now get all records regardless of the Project Code. It seems as if something about this code is causing it to ignore the project code criteria.

Any assistance, guidance, etc. is greatly appreciated.

 

[wiklendt]

rather than making a complex query, why not code the button itself to apply a filter if the date fields are used, and none if they aren't?

 

[bumblingidiot]

rather than making a complex query, why not code the button itself to apply a filter if the date fields are used, and none if they aren't?

Why not? Simply because I tend to go with what I know...which isn't a lot.

I'm not sure what this would look like. Would this be something assigned to my run button? i.e. if fields null then run report for all? If not null than basically run as is? Any further guidance would be greatly appreciated.