Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert date/time from linked Excel workbook (A2K SR-1)

    I have an Excel workbook which is denormalised and contains, among many columns, one containing dates displayed in the format of "Jun-04, Aug-04" etc. (The user needs to keep it as a date type)
    In the link specification this column is a date/time data type.
    I use VBA code, the concept of which was provided on this site, to traverse through the Excel columns and insert these into a normalised table.
    This is the sample of code that transfers the 'date' value
    rstOut!PeriodFinancial = rstIn!Month
    The normalised table has the destination column as a text data type.
    On completion of the process, I end up with '1/07/2004' in the output table when the input date was 'Jun-04'
    How can I ensure that I keep the format of the input date, or transform the input date to what I need
    Currently I transform the date '01/07/2004' using the below, obviously overly complex query.
    PeriodName: UCase(Left(MonthName(Month(CDate([PeriodFinancial]))),3)) & "-" & Right(CDate([PeriodFinancial]),2)

    Any help greatly appreciated.

    <img src=/S/groan.gif border=0 alt=groan width=16 height=15> Zinger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert date/time from linked Excel workbook (A2K SR-1)

    The format 'Jun-04' is only a way to display the date; if you look at the formula bar in Excel, you'll see 6/01/04 (if your system uses MDY date format) or 1/06/04 (if your system uses DMY date format). You shouldn't need to do any conversion to get the date into Access, but if you want to display it the same way as in Excel, set the Format property to mmm-yy.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert date/time from linked Excel workbook (A2K SR-1)

    Thanks Hans,
    I thought I had tied that previously but with no affect.
    Worked like a dream this time.

    Regards,
    Zinger

Posting Permissions

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