Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Linking Excel table to Access

Discussion in 'Information Technology' started by Maurice, Jul 28, 2006.

  1. Maurice

    Maurice
    Expand Collapse
    Guest

    When linking an excel table to Access, some of the data is being displayed as
    : "#Num!". The problem however does not affect all data of the exact same
    nature, but only certain some fields.
    I will appreciate some assistance in resolving the mentioned problem.

    Kind regards
    Maurice
     
  2. Loading...


  3. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Since Access has strong datatyping (every field must have a defined
    datatype) while Excel does not (each cell is independent, a column may
    contain any combination of datatypes), Access must *guess* the
    appropriate datatypes when importing or linking to a spreadsheet. It
    does so by looking at the first few (I don't know the numeric value of
    "few" here <g>) rows. If they are all numeric, Access treats that
    column as if it were of Number type.

    If further down the sheet you have non-numeric data you'll get this
    error. A common example is Zip or PostCodes - if you have five-digit
    numbers in the first several rows, and further down something like
    83660-6354 or N3Y 8B1, you'll get an error.


    One getaround is to put a dummy row at the top of the spreadsheet with
    an unambiguous text value ('X for example) in each such column.


    John W. Vinson[MVP]


    I have had better results if I imported the excel spreadsheet into
    access instead of linking it.

    There can still be problems but fewer. If problems still come up then
    the I have used the approach that John has suggested. Once imported
    then just run a quick query to delete that particular row.

    Ron
     
  4. Maurice

    Maurice
    Expand Collapse
    Guest

    Thank you for really making an effort to provide me with both an explanation,
    as well as a proposed solution. It is much appreciated, and I shall certainly
    give it a try.

    Maurice

    "Ron2006" wrote:

    > Since Access has strong datatyping (every field must have a defined
    > datatype) while Excel does not (each cell is independent, a column may
    > contain any combination of datatypes), Access must *guess* the
    > appropriate datatypes when importing or linking to a spreadsheet. It
    > does so by looking at the first few (I don't know the numeric value of
    > "few" here <g>) rows. If they are all numeric, Access treats that
    > column as if it were of Number type.
    >
    > If further down the sheet you have non-numeric data you'll get this
    > error. A common example is Zip or PostCodes - if you have five-digit
    > numbers in the first several rows, and further down something like
    > 83660-6354 or N3Y 8B1, you'll get an error.
    >
    >
    > One getaround is to put a dummy row at the top of the spreadsheet with
    > an unambiguous text value ('X for example) in each such column.
    >
    >
    > John W. Vinson[MVP]
    >
    >
    > I have had better results if I imported the excel spreadsheet into
    > access instead of linking it.
    >
    > There can still be problems but fewer. If problems still come up then
    > the I have used the approach that John has suggested. Once imported
    > then just run a quick query to delete that particular row.
    >
    > Ron
    >
    >
     

Share This Page