Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Date Column Format Change (Access2002)

    I am downloading data to Excel from PeopleSoft and then I need to get it into Access table.
    My concern is a column wich has Text datatype and gets mm/dd/yyyy sometimes and if people forgot it gets mmddyyyy. So is there way for me to format this column in table?
    Can I simply run a query that will check format and if not mm/dd/yyy - make it this way?
    Thanks

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Date Column Format Change (Access2002)

    Yes, one possibility would run an update query.

    BE CERTAIN TO HAVE A BACKUP OF YOUR DATA """BEFORE""" YOU ATTEMPT THIS.

    The criteria for the date/text column might be something like:

    Not Like "*/*"

    And the update to formula would be (assuming the field name for the column is [ThisDate]):

    Left([ThisDate],2) & "/" & Mid([ThisDate],3,2) & "/" & Right([ThisDate],4)

    REMEMBER: be certain to have a BACKUP of your data """BEFORE""" attempting this.

    Good luck.

    TJ

Posting Permissions

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