Search form with date range or all if values are null

Paula

What do you have so far?

One way to deal with this could be to check if no date(s) have been entered and if they haven't use some date way in the past and one way in the future.

How to implement that, or any other method, depends on how you have things set up.

Take a look at this example.

http://www.datapigtechnologies.com/flashfiles/searchform.html

It should get you started in the right direction.

Rupert

Thank you for the responses...I was temporarily re-routed to another project and now am back on this one...Rupert, I had seen that demo on the form before, but that is only the start of my issue. Norie, I have an OrderDate on all POs. On my search form, users can search by the following key items: user, vendor, department or facility. The secondary items are: POType, Status, and DateRange. Everything works fine and my queries are using the Like & * option on all items. Once I get to the date range, the search form only likes it if the start and end dates are the same. So I am not sure how to set the criteria of the order date to work properly...here is what I have: Like [forms].[frmSearchPO].[txtstartdate] & "*" And Like [forms].[frmSearchPO].[txtenddate] & "*"...I guess my real question is how to use the Between And option with the Like???? Hope I made sense.

Thanks again...

Then take a look here.
http://www.allenbrowne.com/ser-62.html
This sample database does exactly what you want. look at the query in both the SQL view and the QBE grid to get a good idea of what is happening. The explanation provided by Allen Browne should also prove very helpful.

Rupert

Paula

The answer is not to use Like.

Like is a comparison operator used to compare strings.

I've never seen it used with dates or numbers and I can't really see how it would work.

What fields are you actually using in your criteria?

make a new query
view it in SQL View
put this in there, but change the name of your_table to whatever it should be

that should work,
then you can put it back in Design View to see how Access shows it in the query designer

I find it easier to read in SQL View though

so save that query and use it as the RecordSource for the form; put a button or something on the form that the user requests the data with
if the user has put something in the text boxes for the start and end dates, then the query will use it, but if either text box is blank, then the "where clause" of the query will be
where 1 = 1
which is always true, so all rows would be returned if either text box is blank

and you can also include vendor in your criteria that way too

Thank you so much...I will give that a try today and let you know...I really do appreciate the time and effort.