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