Change of formatting from Excel

[BRob]

I've a linked worksheet (Excel 2007 to Access 2007).

A column in excel is formatted as numeric, but it comes into access
formatted as text.

How can I get it back to numeric in Access so I can get the sort sequences
correct

Tx

 

[Klatuu]

Although the Excel column may be formatted as numeric, it is possible in
Excel to put text in a numeric cell. Are there any rows that contain a text
value? That will cause Access to format it as text. Also, check the Excel
sheet to make sure none of the numbers have a ' in front of the number. This
will also cause Access to see it as text. The short of it is, there is
something Access sees in the column to make it think it is a text column.

Another way would be to create an Access table with the correct formatting
and import the spreadsheet into that table rather than linking the
spreadsheet directly; however, if there is a text value in a cell, the import
will fail.

 

[BRob]

Tx Dave that's the problem I think.

The column has a text header and then the rows underneath are all filled
with a conditional formula that can generate blanks (eg = if(A2<20,"",A2)).

Once in access can you overwrite that automatic text formatting in any way?

 

[Klatuu]

Yep, I think it probably is.
Could you have the formula changed to
if(A2<20,0,A2))
That would be the better solution.
If not, another way would be to link to the spreadsheet rather than import
it directly and use a query to append the data from the linked sheet to the
main table and convert the "" to either Null or 0 depending on which you want
in the table.