Hi everyone, after searching these forums for days without findind a solution, I think it's time for me to ask for some help. I'm new to Access, so bear with me (I DO have some familiarity with GIS and SQL). BTW, I'm using Access 2003.

I have one table with many fields, including Date (a datetime field) and Product. The products are processed on various dates. Each record has a distinct ID. For example:

1 7/15/2011 Product A
2 7/15/2011 Product B
3 7/16/2011 Product A
4 7/20/2011 Product A
5 7/20/2011 Product C

I need my form to have (at least) two combo boxes. The first combo box will have all of the dates, and the second combo box will show the products processed on the date selected in the first combo box. Then, various calculations or other data should display.

I'm still stuck on the first combo box. I need it to display distinct dates. I have tried using many SELECT DISTINCT queries, but they all return either a blank list or duplicate dates. I am aware that I probably need to pull the date from the datetime field, but I don't know how to do so and then include it in a query (I have tried DatePart).

I do not want to create a second table to categorize the dates, because the table is constantly updated.

Thanks everyone!

Just to clarify, when I say that I may need to pull the date from datetime, I mean this:
I think that the field "Date" includes time. Even though all that is displayed is "7/17/2011", I think that Access has it stored as "7/17/2011 00:00:00". These records come from a form, where I enter data that gets stored in the main table. Since I enter time into a different field, I think the Date field defaults the time to 00:00:00

I think that my problem with not getting distinct dates arises from the time part of the Date field. I believe I need to extract JUST the date from the Date field.

When I use this in a query:
=DatePart("yyyy",[Date])
I just get a blank table with a column for Date. I know that I will need to use more than "yyyy" to get something like "7/17/2011", but I can't even manage to pull up just the year.


Am I completely off on this?

You can try:
It's easy but not very efficient. A faster but more complex solution:
Then you set the Format property of the combo to "dd/mm/yyyy" AND you keep the date part only when you use the value of the combo.

Thank you, those do make sense to me. Tried this:

SELECT DISTINCT Format([ProdDate], 'mm/dd/yyyy') AS DateRef
FROM [Productivity];

Productivity is the name of the table, ProdDate is the name of the date field. I also tried it without the brackets around Productivity.
and I get the error message below.

I realized what I was doing wrong and was able to display what I wanted (I was typing the whole SQL statement into the "criteria" field in design view).

Thanks!

You're welcome!