Date Criteria from form to query!

[chris89]

I work on a form where I have four comboboxes for : Category , Product, Month , Year , 2 buttons for search and clear .
When I open the form there are all data displayed on a subform.When I choose criteria and hit search the subform filters out the results.
Now I have to add 2 more textboxes for Start Date and End Date.
I ve done this and have put the following expression to the criteria field in the query I am working with:
Between [Forms]![SearchForm]![StartDate] AND [Forms]![SearchForm]![EndDate]

So now I have the problem that I have to enter some dates on the two textboxes so that the subform displays some results else I get a blank subform .

Does anyone know how to display all data bypassing the criteria and if there are dates entered filter them by date?

 

[John Big Booty]

You could use the Nz() function to set a date that is out side of your data range to ensure that when the either field is left blank your search will return a result something like the following should do the trick;

Between Nz([Forms]![SearchForm]![StartDate],"1/1/1900") AND Nz([Forms]![SearchForm]![EndDate], "1/1/2100")

 

[chris89]

I 'm trying to save it on the criteria field under the date in the query but it won't ..

 

[chris89]

I get a message:
The expression you entered contains invalid syntax.
You omitted an operand or operator , you entered an invalid character comma, or you entered text without surrounding it in quotation marks.

I f I hit enter the cursor goes to comma after [StartDate].

 

[John Big Booty]

I've just doubled checked and the code presented in my previous posts works in my sand box DB, as the criteria in a query

So do [Forms]![SearchForm]![StartDate] and [Forms]![SearchForm]![EndDate] represent the names of actual fields on your main form called SearchForm?

Have you check that you have fully copied my code as presented?

 

[chris89]

First of all thank you for your replies!
I ve set [Forms]![SearchForm]![StartDate] and [Forms]![SearchForm]![EndDate] to be general dates on the form so that it gives me a calendar on the right ?
Is that perhaps the cause of error?
And yes these are my actual fields

 

[chris89]

Btw I've downloaded your attachement and I'm not really sure where to look for the criteria you entered!
There is no query to look for, there are only a form and a subform

 

[chris89]

Actually when I enter the above code on the Dates Criteria (on my query) and click anywhere else the code deletes it self.
When I enter :

Between [Forms]![SearchForm]![StartDate] AND [Forms]![SearchForm]![EndDate]

I have no errors on query but have on the form as mentioned above
What is happening???

 

[chris89]

Ok I managed to figured out what the problem was!!
I had to enter the following code:

Between Nz([Forms]![SearchForm]![StartDate];"1/1/1900") And Nz([Forms]![SearchForm]![EndDate];"1/1/2100")

Many thanks to John

Cheers!