Results 1 to 10 of 10
  1. #1
    Jeff_J is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2009
    Posts
    5

    Field Types for a Linked Table

    I currently have a database that I use to do data checks on an Excel spreadsheet. I have the Excel spreadsheet as a linked table and it works OK. I can get the reports that I need from Access and make the necessary corrections.

    The problem lies in the Excel spreadsheet. It has a part number field that contains alpha-numeric(X-1000) and numeric values (30000). When it is linked it inherits the spreadsheet cell type. I have it set it to general so that I can get the alpha-numeric values, which are the majority in the spreadsheet. I would also like to find a way to get the numeric values lines as well.



    My initial solution was to have two version of the Excel spreadsheet and format them according to the values that I want. Then link them in Access and use queries to create one table that I can run the data checks on. The problem is with the numeric version of the spreadsheet. It will not link since it has both types of values in the cells.

    Is there another way to get this accomplished?

    Software versions
    Excel: 2007
    Access: 2002

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would think it should be a Text field. You can always get the Val() of the field if it is numeric.

  3. #3
    Jeff_J is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2009
    Posts
    5
    I have tried that and the numeric values still come in as #Num!. I am not certain about the Val() that you are referring to though. Is that what I have to do next to get them to display? And how do I go about accomplishing it?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    PartNumbers are generally not used for calculations so there is no reason for them to be anything other than a Text field. Are you saying that Excel makes them a numeric field? I believe if you just preceed the value with a ' it will change to a text field.

  5. #5
    Jeff_J is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2009
    Posts
    5
    They appear absolutely correct in Excel no matter what I set the cells too. It is when they get imported or linked to in Access that they are not accurate.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So you believe Access is interpreting these fields as numeric when they begin with a number? Any chance you can post a small sample of the Excel sheet with both types so I can link to it?

  7. #7
    Jeff_J is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2009
    Posts
    5
    Here is a sample. The first line works great and accounts for the majority of the list. The second produces the #Num! in Access even though it is accurate in the spreadsheet.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I went in to the xls file and added a tick (') to the beginning of the second record and then deleted the linked table and linked again. This time Access used a Text field for the data instead of a number. It now reads just fine in access.

  9. #9
    Jeff_J is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2009
    Posts
    5
    That worked, but creates another problem. Is there a way to update several records without having to manually touch each one? I create the spreadsheet from an inventory one with an Excel macro and then go into Acccess and run my reports. If I were to have to touch each record that would take a considerable amount of time. I will also see what I can find too.

    Thanks for the help, at least I have a way to get around the issue.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can highlight a column and switch the entire column to Text at the same time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linked Table Field Validation
    By yuriyl in forum Access
    Replies: 4
    Last Post: 04-20-2009, 01:31 PM
  2. Linked table to sql 2000 db
    By tcroninstl in forum Access
    Replies: 1
    Last Post: 03-23-2009, 07:27 PM
  3. Editing a Linked Table
    By amndza in forum Access
    Replies: 2
    Last Post: 01-21-2009, 01:27 PM
  4. Possible to store user-defined types in table?
    By Binky in forum Programming
    Replies: 0
    Last Post: 11-20-2008, 02:28 PM
  5. Linked Table Manager
    By driccardi in forum Access
    Replies: 4
    Last Post: 02-15-2006, 11:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums