Results 1 to 5 of 5
  1. #1
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,016
    Thanks
    245
    Thanked 147 Times in 125 Posts

    Access 365 change Date Format of table contents

    I want our dates in mm/dd/yyyy format, ie 2 digits for month & day incl leading zero, & 4 year digits. I've changed the input mask from…
    99/99/0000
    to…
    00/00/0000
    …which should take care of this going forward.

    Is there an easy/auto way to change the existing table contents?
    The only issue is the leading zeros for mm & dd.

    PS fyi re Input Mask:
    "9" means user MAY enter a digit;
    "0" means user MUST enter a digit
    Last edited by Lugh; 2018-07-08 at 17:59. Reason: PS
    Lugh.
    ~
    Dell Alienware Aurora R6
    Win10 Home x64 1709; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    2 x 256G SSD, 4TB HD

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,916
    Thanks
    63
    Thanked 1,115 Times in 1,037 Posts
    Wouldn't you be better off saving the data as a date and then formatting it as required for display?
    That input mask would allow dd/mm/yyyy input, e.g. 07/06/2018. It may be worth displaying it as dd MMM yyyy so users can be sure they got it right.

    cheers, Paul

  3. #3
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,016
    Thanks
    245
    Thanked 147 Times in 125 Posts
    Quote Originally Posted by Paul T View Post
    Wouldn't you be better off saving the data as a date and then formatting it as required for display?
    Thanks Paul. Yes, you are right, this is what we do everywhere else.

    Our recent problem is with a large outside company we upload info to. They provide a spreadsheet template, and the latest version of it somehow prevented reformatting 7/6/2018 to 07/06/2018—problem being dates without leading zeros caused our upload to fail.

    They fixed the problem yesterday, but have a history of messing things up with untested changes to various systems, so I try to foolproof our end as much as possible.

    However…

    I've probably got this wrong. Access probably can't store a format with a date, can it? Ie format is always a display choice.
    Lugh.
    ~
    Dell Alienware Aurora R6
    Win10 Home x64 1709; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    2 x 256G SSD, 4TB HD

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,941
    Thanks
    418
    Thanked 1,593 Times in 1,445 Posts
    Lugh,

    You can set the format in the Table Definition.
    AccessTableDateFmt.JPG

    Result:
    AccessTableFmt.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Lugh (2018-07-10)

  6. #5
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,016
    Thanks
    245
    Thanked 147 Times in 125 Posts
    Quote Originally Posted by RetiredGeek View Post
    You can set the format in the Table Definition
    Oh you beauty! I didn't realize you could type directly in there, thought I had to select one of the dropdown choices.

    That did the trick, changed all the existing content. Thanks
    Lugh.
    ~
    Dell Alienware Aurora R6
    Win10 Home x64 1709; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    2 x 256G SSD, 4TB HD

Posting Permissions

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