Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Unanswered: Select distinct month/year

    I have a little quandary, probably not too difficult to solve. I'll just make this simple:

    I have a table tblDosageINR, with a column dteCollDate, which would be a short date field to hold the month/day/year a patient's lab results were collected.

    I want to write a query that will select distinct dates, but I only want the distinct months and years, not months, days, and years.

    Is there a function in MS Access that will allow me to do this? If not, I may have to change a few things.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Semi-fix

    Code:
    SELECT DISTINCT DatePart('m',  dteCollDate) AS Month, DatePart('yyyy', dteCollDate) AS Year
    FROM tblDosageINR;
    This is the...partial fix I've employed, but the month and year are in separate columns. : \ I'm not entirely familiar with using the DatePart function, so when I tried to put ", 'yyyy'" after 'm', it...didn't like that very much.

    Any input, very appreciated.

    Quote Originally Posted by atsukoarai86
    I have a little quandary, probably not too difficult to solve. I'll just make this simple:

    I have a table tblDosageINR, with a column dteCollDate, which would be a short date field to hold the month/day/year a patient's lab results were collected.

    I want to write a query that will select distinct dates, but I only want the distinct months and years, not months, days, and years.

    Is there a function in MS Access that will allow me to do this? If not, I may have to change a few things.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The problem here is there is no "month/year" datatype available in Access. You get datetime, or datetime...

    How about taking a slightly different approach and converting your dates to be the 1st of the month?

    I'm using DateAdd() here to avoid an explicit conversion between a string and a date. Another way would be to pull the month and year, then inject a "/1/" between them and convert to a string. I think the performance hit there is unacceptable:

    SELECT DISTINCT DateAdd('d', -DatePart('d', dteCol1Date) + 1, dteCol1Date)
    FROM yourTable
    Last edited by Teddy; 07-09-09 at 18:21.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Hmmmmmm... I will see how that works.

    hmm... I'm having a brain fart right at this very moment... trying to articulate why I'm doing this.


    Quote Originally Posted by Teddy
    The problem here is there is no "month/year" datatype available in Access. You get datetime, or datetime...

    How about taking a slightly different approach and converting your dates to be the 1st of the month?

    I'm using DateAdd() here to avoid an explicit conversion between a string and a date. Another way would be to pull the month and year, then inject a "/1/" between them and convert to a string. I think the performance hit there is unacceptable:

    SELECT DISTINCT DateAdd('d', dteCol1Date, -DatePart('d', dteCol1Date) - 1)
    FROM yourTable
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

Posting Permissions

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