Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Question Unanswered: Combo Box/Form Issues- Distinct Date?

    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!

  2. #2
    Join Date
    Aug 2011
    Posts
    5
    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?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can try:
    Code:
    SELECT DISTINCT Format([DateColumn], 'mm/dd/yyyy') AS DateRef
    FROM [TableName];
    It's easy but not very efficient. A faster but more complex solution:
    Code:
    SELECT DISTINCT [DateColumn] AS DateRef
    FROM [TableName];
    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.
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    5
    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.
    Attached Thumbnails Attached Thumbnails error.bmp  

  5. #5
    Join Date
    Aug 2011
    Posts
    5
    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!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •