Between Dates Parameter Prompt w. Leave Blank Option

[JewliaB]

Okay, I apologize because I know this question is probably overworked and I searched the forums for something along these lines, but I apparently haven't been able to find the right search criteria to find the posts on it. I am trying to have a parameter prompt in a query where the user enters a start date and an end date for the report and the query then pulls records between these dates for the report. I got this part, but I would also like a leave blank option for the user such that if they leave both dates blank, the form will show all records, or if they leave the start date blank it will pull all records until the end date and if they leave the end date blank, it will pull all records from the start date on. I have attempted it as such:

Between (([Database].[Date])=[Please enter the start date for the report:] Or ([Database].[Date]) Like [Please enter the start date for the report:] Is Null) And (([Database].[Date])=[Please enter the end date for the report:] Or ([Database].[Date]) Like [Please enter the end date for the report:] Is Null)

However, this causes no results to return at all no matter what. If someone could eitherpoint me to another thread on this particular subject and/or provide assistance, I would appreciate it.

Thank you in advance,

Jewlia

 

[HiTechCoach]

NOTE: You should avoid using reserved words for user named objects. Date should not be used as a field name. I find it will cause you lots of headaches.


AFAIK, when using a Between in the criteria you must have a valid starting and ending values. Neither can be Null.

I have found to best to always enter the date for the range.

I would use a form to collect the values and then calculate the WHERE based on the users input.