Using * in date combo box in Access

I have a Sub Form that retrieves from a combo box using a date.  I would like to have the option to use "*" as I would for a combo box with a string value.  Does such an option exist for dates?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you post the code or query you are currently using to limit the subform data?

My usual approach is to provide two boxes for a date range, and instead of using a *, let the user enter blanks in either the From date or To date box.  The query criteria then looks like this:

WHERE [MyDateField] BETWEEN NZ(Forms!MyFormName!txtDateFrom, [MyDateField]) AND NZ(Forms!MyFormName!txtDateTo, [MyDateField])

Open in new window

This approach assumes that if the user enters blanks in the boxes, then return all dates.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marku24Author Commented:
Using two date fields worked great.  I should of thought of that.  Thanks
Dale FyeCommented:
You could add an "All dates" option to the combo using a union query.  I have another table in most of my databases (tbl_Numbers) which contains one field (intNumbers) and 10 records (the numbers 0 - 9).  Then I create a query that looks something like:

Select DateField
From (
Select intNumber, "All dates" as DateField
From tbl_Numbers Where intNumber =0
Union all
Select DISTINCT int(yourDateField), YourDateField
From yourTable)
order By  intNumber

Then, in your query, you would use something like:

Where [YourDateField] = iif(Forms!YourFormName!cbo_Dates = "All dates", [YourDateField], cdate(Forms!YourFormName!cbo_Date)

Typed on my iPad, so untested and may contain autocorrect errors.
Dale FyeCommented:
That's the problem of working on an iPad, too slow!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.