linked field is text

[Guest]

I have linked an excel sheet to access. I have made sure that I have
formatted some excel fields a text because they contain text and numbers.
However when linked, the numbers appear as #NUM!

 

[Guest]

Make sure the fields in Access are set as the text datatype. Typically,
Access looks at the first several (8, I think) rows and makes a decision
based on that if it isn't forced. You can use a dummy row with text for the
first data row in Excel if you need to force it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I've sorted the excel records so that the text records are first, and then
relinked the data base. I still get the #NUM! resault. The said field is
described as a text field in access. I note that the field properties box in
access shows the format as @ and will not let me change it.

 

[Arvin Meyer [MVP]]

I'm not sure I can help with that, but let me try 1 more thought. Look at
the Excel cells and see if each value is preceded by a ' (single quote).
Excel may be telling the "world" that the valyes are numeric.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

I'm nor sure how to find out if is is a single quote, and if so what do I do?

 

[Van T. Dinh]

Select a cell with what looks like number in your Excel spreadsheet and look
at the value in the Formula bar. If the value looks like:

123 then it is a numeric value.

If it looks like:

'123 then it is a Text value.

If you don't apply any alignment format in the cell, text value is aligned
left and numeric value is align right by default.

What I nornally do with a mixed Column in Excel, says, Column A, is to
create an extra Column next to it and use the Spreadsheet function Text(
{cell} , "General") to convert both Text and Numeric values to purely Text.
When I link or import the data from the spreadsheet, I ignore the original
column and use the (calculated) column whose values are all Text.

 

[Guest]

Got it thanks.
But why does the "fomat cells" as "text" not take care of this?

 

[Van T. Dinh]

Format cells as "Text" only changes the display in Excel, not the values.

The calculated Column using the spreadsheet function TEXT() actually creates
Text values.