Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    Tables Linked to Excel97 (Access97/SR2 )

    I am having problems with date formats. In excel I will format a cell or the whole column as a date format of 02/20/02. However,
    once I create a new link table in access the data in the table is #Num! in every cell that has a date entered. When I look at the table
    I can see that my date field data type shows Text when it should show Date/time, I think.
    Yes I need to keep the data updated in the excel sheet. At least for a while.
    1) Is there any way to update only the data type in the access table that is linked to an excel spreedsheet??
    2) Is there any temporary way to unlink a table just so I can update the data type??
    3) is there any in creating a linked table to exclude more than one title row?? And for that matter exclude or include 1 thru n rows??
    Any thoughts or ideas are welcome..

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Richland, Washington, USA
    Thanked 0 Times in 0 Posts

    Re: Tables Linked to Excel97 (Access97/SR2 )

    Here are a couple possibilities:
    * If your Excel date data is Format...Cells...Number...Date formatted (and isn't text) then the linked table in Access should give that field a Date/Time data type by default (it does for me in Access 2000; I don't remember it being a problem with Excel/Access 97).
    * If that continues not to work, you could use a Make Table query to "copy" the linked table into a "native" Access table. You can then change data types on that native table.
    * If your Excel table has a fairly simple structure, you could save it as a comma separated value (CSV) or tab-delimited file. The Link wizard in Access for these file types gives you more control over how the data is imported (including forcing a field to be the date/time format).
    * I don't know of a way to exclude more than one title row. You could create a make table query that would extract the extra rows (e.g., use the IsDate() logic function in the criteria) to create a new table without the extra title rows.

    Hope this helps some.

Posting Permissions

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