Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Linking Excel File to Access (XP)

    I'm trying to link an Excel file .xls (exported from a custom app) so that I can manipulate the data and write Access reports. Two fields are giving me fits. These fields hold some alphanumeric entries and some straight number entries. My problem is the reverse of what you'd expect. Access sets the data type as text and the alphanumeric entries come through correctly. The numbers (integers) come through as #Num!. This is school information - and the grade field displays KA and KP (for morning and afternoon kindergarten) but not 1, 2, 3, etc.

    My original export from the application can be saved as a .csv file, but doing this loses date fields.

    Your help and explanations will be appreciated.
    Thanks

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

    Re: Linking Excel File to Access (XP)

    That sounds very strange - why would Access display #Num in a text field?

    Does it work correctly if you import the Excel file instead of linking it?

    If you like, could you post a small but representative sampe file (replace sensitive date with dummy data)?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking Excel File to Access (XP)

    I tried to send you a couple small files - a tiny sample Excel file and the Access file as well. I was unable to do it - maybe because of settings here at the office. If I can't get them done, I'll take them home and try again.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Linking Excel File to Access (XP)

    The file was simply too large. I've zipped it and will try sending it that way. I look forward to your comments

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

    Re: Linking Excel File to Access (XP)

    The behavior is certainly strange. I con't remember having seen #Num in a text field, but it does happen the way you described it. The only workaround I can think of is to create a table with the correct structure and to import the Excel data into this table. See attached demo.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linking Excel File to Access (XP)

    Access has interpretted the Grade column as a double precision number field for some strange reason. Then when you have alpha characters in this type of field it will show as #num. Change the Grade column as a text field (Hans has already explained this).

Posting Permissions

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