Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting a date field in a table

    How can I format a date field in a table to show month and day no year September 4

    Thanks.

    Paul

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Last edited by RetiredGeek; 2013-09-04 at 13:24.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG-

    Ok - now I have the format correct. How can I sort the Month Date field so that all the September dates are in order 01-30 without taking into consideration the year. Apparently the year is still in the field even if you can't see it.

    Paul

  4. #4
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Technically, years aren't stored in the date field, as the number in the field is a numeric and is the number of days since a particular date - quite a long time ago (I forget which); the field you see is formatted in the requisite date format (either as the link suggests or your own formatting). The only way to sort as you want is to separate the data components of the date (dd, mm, yy) and sort that way - probably a bit of VBA will do the trick, but as you don't say how your displaying this (form, query, table) it's difficult to be more precise, but the function datepart should be the one to use. This is from Access 2003, maybe a newer version has a better way?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Here's how to setup your query.

    Table Data:
    MonthsortIgnoreYearTabledata.JPG
    Query Design:
    MonthsortIgnoreYearDesign.JPG
    Query Results:
    MonthsortIgnoreYear.JPG

    All Month entries have different years if you view the whole date.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    access-mdb (2013-09-05)

  7. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There's a bottom line here that is implied, but not stated directly. In general you don't want to be viewing data in tables - view them in queries or forms instead. The format RG gave you just hides the year, but when you try to sort it at the table level, it sorts based on the underlying data.
    Wendell

  8. #7
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Thanks RG, I had forgotten about the month /day function, though datepart does work (in 2003 at least) e.g. mymonth: DatePart("m",[date shot]). But using your suggestion is more elegant!

  9. #8
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RG.

    You came up with a simple solution. Problem solved.

    Paul

Posting Permissions

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