MakeTable Query won't create text data type?!?

[AnnK]

Hi all,

Access is being quite stubborn with me! I have set up a MakeTable query from a linked Excel spreadsheet. One of the columns of the spreadsheet is formatted as text because it has a diverse set of entries (including words, numbers, and letter/number combos). But when Access pulls up this data, it gives a #Num! for each word and each number (it it happy with the letter/number combos).

I've reformatted the column to text a number of times (in Excel). I've recreated the link after reminding Excel that the column was text. I even tried to add a set of dummy rows at the beginning with the word 'text' in that column in case the variety was causing a problem. All attempts were in vain- I still get the #Num!.

Is there anyway to force a data type in a linked or queried table? Or does anyone know why it is misreading this column?

[Mariostg]

Whatever format you pick, Access looks at the content. If the first entry in a given column is number, it will create a numeric field. Assuming in row one of your Excel sheet you have the header titles, insert in row two some data that mimic the field type desired in each column.

[AnnK]

Thanks Mario, but I already tried that:

"I even tried to add a set of dummy rows at the beginning with the word 'text' in that column in case the variety was causing a problem."

Specifically, I added 10 rows with text in that column because I saw somewhere that while Access uses the 1st 25 rows to autodetect a data type when you import data, it only looks at the first 9 rows in a query. I still get the error.

[ADezii]

Hello AnnK, can you Upload a sample of the Excel Spreadsheet with the offending Column and Sample Data?

[AnnK]

I neglected to include that both my programs are the 2003 version, which are running on XP.

[AnnK]

ADez,
I tried to upload a small version of the spreadsheet- but it seems that Excel is not one of the file types allowed, so I encased it in a zip file. I hope that works.

[Mariostg]

Ann, what are the stubborn field names, it might me easier for us to spot the problem?

Besides, you should avoid using "special" characters in you headers when you know you export your spreadsheeet to Excel. I refer to characters like @, #, /, (, ), ' and even spaces. Problem you can run into is like Access may want to execute a division with / in some circumstances.

[ADezii]

I do have an answer for you, but I am looking for a simpler approach right now. Setting the Format to Text only controls the actual Formatting of the Values. For some reason, the Errors occur only on Numeric Values occurring in the Column, specific Column 6 (Location), and with the actual Linking process from the Access end. If I explicitly Convert all Numeric Values in the Location Column to Text, then everything shows up fine when the Spreadsheet is Linked. I'll look into it further this evening at work.

P.S. - Ironically, if you Import the Spreadsheet as opposed to Linking it, all works well.

[Mariostg]

Moreover, after linking it, if you retype a number as is in the spreadsheet, it shows up properly on the linked table... Interesting.

[AnnK]

Sorry, for anyone who hasn't found it- it is column F- the "location" column that is giving the error.

And I too found that if I retyped the data it worked. But with a 20000 row sheet, I'd like to find another solution. It also seems odd that there are other mixed columns that it doesn't seem to care are mixed up (i.e. the 'batch' column which also has some numbers, some text, and some letter/number combos).

I was originally trying to do it via import, but was advised that since I'd have users updating the spreadsheet daily, it would be faster to do the transfer via a linked spreadsheet and 'makeTable' query. (The idea being that having Access check each line in order to update the table would use more network resources- and our network does tend toward slow. Just recreating it would be faster. Was this poor advice?)

[AnnK]

How do you convert the numeric values to text? I was looking for some way in Access to change the data type but couldn't find one :( Ideally I'd need to write that into the query so it would do it each time it created the table. Of course, if it was done in Excel- the data would transfer the same each time.

[MMcCarthy]

Hi Ann the CStr() function should do what you want.

SELECT field1, CStr(field2) AS NewFieldName
FROM tablename

[ADezii]

I still cannot explain this strange behavior, and I really do not have the time right now to investigate it in length, but I do have a solution for you that works. Executing the following Code will analyze the 20,000 Rows in the Location Column (Column 6) and if there is a Numeric Value in that specific Cell, will explicitly convert it to Text. Once this Code runs, and the Spreadsheet has been saved, it can then be Linked in an Access Database with all Values being displayed correctly. It ain't pretty, but it do work! (LOL).

Dim rng As Excel.Range
Dim rngData As Excel.Range

Set rngData = ActiveSheet.Range("F1:F20000")

For Each rng In rngData
  If IsNumeric(rng.Value) Then rng.Value = CStr(rng.Value)
Next

MsgBox "ALL Numeric Data in Column F has been converted to Text", vbInformation, "Data Conversion"

[AnnK]

ADez,

Thank You Thank You Thank You!!! That works superbly, and doing it in the spreadsheet was the ideal solution. It's better than pretty, it's gorgeous!

Thanks again!

[ADezii]

You are quite welcome, but why this happens is still a mystery! (LOL)