Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Export report to Excel Date field displayed as number.

    Have a report based on a query. One field ( RefresherDate) in the query consists of a date arrived at by using the Switch function on Text and Date fields in the query.
    Simply put it returns the advised refresher dates for certain training courses based on when the course was last taken.
    The query outputs the RefresherDate field in date format fine if I just run the query. A report based on the query outputs the field in date format fine if I elect to have the report output as rtf but if I choose to export or 'save as' the report in Excel ( 97 -2002 ) format the RefresherDate column in the resulting spreadsheet comes out as a number. I can reformat the column as a Date and it then shows the correct date.
    I have a similar query- report - output to excel situation but the query at the start of this one uses the IIF function, this ends up with the date in correct format in the spreadsheet.
    Have tried altering the properties of the RefersherDate field in the query and the report to Date, it still ouputs a number in the spreadsheet.
    So some combination of the use of the Switch function and exporting to Excel from a report rather than straight from a query is causing Excel to interpret this field as a number value. Have googled and searched this forum to no avail , any help appreciated
    Thanks
    Keith

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I have had similar issues in the past. One thing that I have done is to output the date field not as a date format but just as a string format using CStr() function. Otherwise, you can create code to handle putting the data into an Excel file where you can control the format of the columns yourself.

    C

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Thanks will investiaget the String function first
    Keith

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I too had a similar problem where I had to reformat the date and times columns. I solved the problem by exporting the data (use some test data) to the spreadsheet then formating the data with the required format. Save the spreadsheet, then export your "live" data to check that the spreadsheet has the new data in the correct format.

Posting Permissions

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