Convert Text to Number

[Jerseynjphillypa]

I have an excel table that I am linking, but it keeps showing up as a Text field while the other tables show up as a Number field. How can I change the Text field into a number field.

I tried going to the excel source file and changed the column format to Number but it still wouldn't change to a Number field when I tried to link the data again

[June7]

Do you want to provide the Excel file for analysis?

[Jerseynjphillypa]

here is an example of the database.

the excel file that i link in the db, i need the text column to appear as a number field

[June7]

Need numeric data in the Excel column then Access will treat it as a number field. Put something, even just 0, in at least the first data row.

[Jerseynjphillypa]

Thanks for the help.

Since any number, can be a valid answer including 0, but a blank cell indicates that I no longer use the item anymore.

[June7]

That's the only way I could get the field recognized as number. However, even as a text field can still use the values for calculations. Sorting won't work right.

[Jerseynjphillypa]

Not sure what you mean by shorting?

Since I am using the union function to combine multiply tables to one, I have other link tables (20) from excel being pull as a number field while (5) other tables are being pulled as text fields since they all have blank values in the column.

[June7]

Sorting - ordering by highest to lowest or lowest to highest. Numbers seen as text will not sort numerically, subject to alpha sort, e.g. 12 would sort before 9.

Try using Val function on the fields seen as text when building the UNION queries if you are getting a type conflict error.