linked field is text

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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.
 
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.
 
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.
 
Back
Top