Access Report not showing mixed values from linked Excel

[bzpilot]

I have an excel database linked to a table and created a report.

The issue is that on some fields in the report data the value is not displayed. Went back and checked the linked table and the table shows these undisplayed items as "#Num!". These columns in the Excel database contain mixed values of data, some numbers some text. The "#Num!" displays after there is change from what the initial row of data contained in that column (i.e. if the first row item was text, when a number in the column shows up it displays "#Num!" in the table and nothing in the report - and vice versa if the first row item was a number, when text shows up it displays "Num!")

I checked the Excel database column properties and made sure they were all set to TEXT (a few columns were set to general, so I changed them). Still get the same results. Tried unlinking the database and relinking, still nothing.

Any help would be appreciated. Thanks.

 

[Joe4]

Try this:

1. Remove your link altogether.
2. Change any column with mixed formats to a Text format.
3. Highlight the column and do a Text to Columns, selecting the Text option in Step 3. Repeat for all offending columns.
4. Save the file.
5. Re-establish a new link in Access and see it that works.

I know that Access does not like mixed format types in the same column. If this does not fix the problem, as a last resort you could export your Excel data to a text file and then import the text file manually. Through the import wizard, you would have complete control of the format of the incoming data.

 

[bzpilot]

The only way I got it to format correctly was by exporting by CSV. I tried tricking it 100 different ways with some other ways people had posted on the internet but none worked.

This is going to be a real pain because I have about 10 worksheets in my excel database that change almost daily and need to be linked to access reports. Exporting all these seperately doesn't sound like to much fun to me!!!

Any idea why Microsoft hasn't change this. After researching it looks like people have had this same issue since Access 97 and it still doesn't work in Access 2003.

 

[SydneyGeek]

The reason for the problem, as jmiskey pointed oiut, is that Access is pretty strict about the data types in fields. A field can hold, for example, Date/Time, Number, or Text, but not all three at once. If you do want to hold mixtures, you have to convet the whole thing to text and lose any intelligence about dates, number sorting, or whatever.
It's not just an Access issue -- all SQL databases that I have encountered are similarly strict. Yep, it's a pain if you are used to mixing whatever you like in Excel, but there are good reasons for it

Denis

 

[bzpilot]

Even after converting all columns to text only before linking to Access I am still getting errors (#num!) in my reports.

The only way I've found to circumvent this is by exporting via tab seperated .txt. Unfortunately I'm going to be dealing with multiple worksheets that are going to need to be exported on a regular basis. Where if a I could just link it would save HOURS of time.

I am definitely going to have to find another way around this!!!

 

[SydneyGeek]

Is there a way of rethinking your calculations in Excel, so all values in a column are numbers OR text, but not both? I'm assuming that the mixed Numbers / Text issue is the result of an IF statement returning values or text flags depending on the conditions -- is that correct?

Denis