Using * in date combo box in Access

Posted on 2013-10-21
Medium Priority
Last Modified: 2013-10-21
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?
Question by:marku24
  • 2
LVL 61

Accepted Solution

mbizup earned 900 total points
ID: 39587856
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.

Author Closing Comment

ID: 39587985
Using two date fields worked great.  I should of thought of that.  Thanks
LVL 50

Expert Comment

by:Dale Fye
ID: 39588036
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.
LVL 50

Expert Comment

by:Dale Fye
ID: 39588043
That's the problem of working on an iPad, too slow!

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question