Report Format Stumper

[liamrowan]

I have an Excel file that is linked to an Access table. In the Excel file there is a column formatted as a custom number. But in the corresponding column in the linked Access table the data doesn’t show up because the column in Access is formatted as “Text.” What is showing in the column in the linked table in place of numbers is "#Num!" Since the Access table is linked, it won’t allow me to change the format from text to number.

I have tried three solutions. In the original Excel file, I tried selecting the column and specifying the format as "Number" and then as "General." In either case the column still shows up as text in the linked access table. There are other columns in the Excel file that have the same custom number format but are pulling correctly into the linked Access table. I used the Format Painter in the Excel file to copy the format of one of these columns into the problem column, but the problem column still shows up in the linked table as text.

I just can’t figure out why the linked Access table is not picking up the formatting of the column from the Excel file on which it draws. Any ideas?

Thanks, William

 

[boblarson]

In Excel, make sure that the cells all have valid numbers and highlight and delete all non-used rows (or a good number of them on down) so that any with an empty string might be removed. Sometimes an empty string will be there but it shows as a blank but Access would pick up on it as not being a number so it would have to use TEXT instead of number since an empty string can't be entered into a number field.

 

[gemma-the-husky]

are the numbers too big to be handled in access? - 10 digit numbers are likely to be out of access's range.

 

[liamrowan]

No 10-digit numbers, but thanks for that tip as it might be an issue later.

 

[liamrowan]

Bob,

There were indeed some empty rows that were showing up and I deleted those. There are fields in the problematic column that need to have nothing in them (not sure if that means empty string). I selected them, hit the delete button to clear unseen content, and then formatted as number. All the data and the empty fields then successfully pulled into the linked table.

I recognize your name from having helped me before. Thanks so much for taking the time to share your expert advice; it would not be possible to describe how much time and trouble you have saved. I work in a state Department of Education and often there are deadlines which can easily get blown with a quirky software issue. Please know that your are deeply appreciated.