Date Query for Combo Box & help w/ 2nd Combo Box

[ewassmer]

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).

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?





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

Thanks everyone!

[pbaldy]

Does this work to get a distinct list of dates?

SELECT DateValue(DateFieldName) AS DateOnly
FROM TableName
GROUP BY DateValue(DateFieldName)

"Date" is not a good field name, as sooner or later Access with confuse it with the built-in Date() function.

[ewassmer]

Thanks for the tip, I changed "Date" to "Day" to avoid issues.

I used this code:
SELECT DateValue(Day) AS DateOnly
FROM Productivity
GROUP BY DateValue(Day)


and got an error message (img attached to this post).

[ewassmer]

I also tried it by replacing Day with [Day] and got the same message (sorry, warned you I was new at this...)

[pbaldy]

Curious, as I just tested this and it worked correctly on a date/time field that has a time in it:

SELECT DateValue([ReqDateTime]) AS DateOnly
FROM tblReservations
GROUP BY DateValue([ReqDateTime])

By the way, Day is also bad (there's a Day() function). Both are what's called reserved words. Here's a basic list:

http://support.microsoft.com/default...b;EN-US;209187

and a really comprehensive one:

http://allenbrowne.com/AppIssueBadWord.html

I don't think that's your problem though, as the brackets would have resolved it. Can you post a sample db here with that table in it?

[ewassmer]

Changed the field name again, still no luck.
Sample db is attached.
Thank you!

[pbaldy]

I'm not getting any errors with this:

SELECT DateValue([ProdDate]) AS DateOnly
FROM Productivity
GROUP BY DateValue([ProdDate]);

and I don't think there's a time in there, as this returns the same records:

SELECT Productivity.ProdDate
FROM Productivity
GROUP BY Productivity.ProdDate;

[ewassmer]

I tried both and I still get the same error message.

I put the syntax into the combo box under Row Source, and nothing came up in the combo box (blank).
I also tried putting the syntax into a query in design view under Criteria and got that error message.
I attached photos to show you.

Your patience is much appreciated!

[pbaldy]

What I posted was the entire query, not just a criteria. Copy/paste that into SQL view of a new query or the combo rowsource. If it's not working as the combo rowsource, note that the properties of that combo are set to hide the first column (see the column count and column widths properties).

[ewassmer]

Perfect, it's showing up now! Phew, thanks!

Now, my next question... how can I get the 2nd combo box to display all products related to the date selected in the 1st combo box?

The example queries I found for the second combo box are based on a separate table from the first combo box. However, all of my data is in that one table.

[pbaldy]

What you want is typically called cascading combos:

Baldy-Cascading Combos

The table structure isn't really relevant, as you just have to create a query or SQL that pulls from wherever is appropriate.

[ewassmer]

Great! Got both combo boxes to work.

Now, from that 2nd combo box, how can I display certain data on the form? This data will pertain to a specific product on a specific date. Ideally, when you select the product in the 2nd combo box, a list of data/calculations would appear.

[ewassmer]

Maybe this involves creating more combo boxes? I would want the data to be separate from the 2nd combo box.

[ewassmer]

After looking around, I think I would like the selection of a product in the 2nd combo box to autofill list boxes with data of my choosing. is this at all possible? thanks.

[pbaldy]

If the data can be included in the row source of the second combo:

http://www.baldyweb.com/Autofill.htm

Otherwise, you'd probably need to open a recordset and get the values that way.