Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting \ into a text field to make it a Date (Access 2k win 2k)

    Hello again

    Sorry to be asking a question I think has been answered, but I can't find the previous threads. Don't really know what the right question is.....

    I have an Excel file (created from a third party package), in this file there is a date filed formatted 01022003, ddmmyyyy, with no separators. Now Excel can and will sort this into dd/mm/yyyy and we have been doing it this way for a while. The demand for the data has increased and it would be nice if I could get Access to format the date correctly. I've looked at the 'left', 'mid', 'right' options, but they don't seem to do what I want.

    I've got a macro recorded in Excel that does the format OK, if Access can't insert the date separator can I use VBA to call the Excel macro, it's in a separate work book to the one I want to format.

    Thanks in advance

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Inserting \ into a text field to make it a Date (Access 2k win 2k)

    There are a couple of alternatives. One would be to simply format the date and leave it in the text format. But there are significant advantages in storing it in native Date/Time format within Access. Have you tried formatting the date in Excel as a US style date, and then importing it into Access.

    The other option would be to bring it in as text, then use the following expression to populate a new column defined as Date/Time:
    <font color=blue><font face="Georgia">DateSerial(Right([DateText],4), Mid([DateText],3,2), Left([DateText],2)</font face=georgia></font color=blue>
    where DateText is the name of the text field containing the 'ddmmyyyy' data.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting \ into a text field to make it a Date (Access 2k win 2k)

    Wendell

    Thank You. Another case of my not knowing what to ask for solved.

    I've used the DateSerial solution. The Excel spreadsheets are replaced each night by our MRP system, so I'll be using Queries with your solution as the data source for my forms, just a case of changing the data source in a forms property setting.

    Thanks again

    Ian

Posting Permissions

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