Linking excel data in number and text form with access

K

KIM

I understand that access uses the first 15 rows of an imported excel sheet to
determine whether the access field is numerical or text. I have a worksheet
with a date column, and columns that contain both numbers and text entries
(in the form of less than values e.g.<1). Therefore the date column cannot
be changed to text or number otherwise it looses the correct format. And
although the numbers can be changed to text in excel they are only recognised
as numbers in access. I need all the data in the format i entered it i.e 12,
34, <1, <100, etc. It is not practical to invent a convention to try and
show the < values as a number. The only way I have found to get the all the
information across from excel into access without error values (e.g.#NUM!) is
to enter ' in front of all entries. I have done this manually (i.e. entering
each cell separately) but this is not practical for the large amount of data
I have. Is there any other way round the problem? If not is there any other
way of getting ' in front of every entry without having to do it manually? A
macro perhaps? If I do have ' in front of every entry can I still calculate
average values for the columns?

Thank you
 
M

[MVP] S.Clark

You could do an update query. Where there is a <, update it with the
apostrophe in front of the data.

Update tablename set [f1] = "'" & [f1] where left$([f1]="<"

(Or just make a select query, and use it for the basis of the export.)
 
M

MacDermott

It's a common fallacy to think "Excel for small needs - Access for larger
projects".
Actually, there are some kinds of projects where Excel is always going to be
better suited, and others for Access. Size of the project is not the only
criterion.

Yours sounds like a typical Excel project.
In Access, each "column" can contain only one type of data.
<100 is not numeric data -
suppose you had column entries of 50,235, and <100 - how would you
average them?
Excel, however, has no trouble with a variety of entries in the same column.

Do you have a compelling reason to try to do this in Access?
- Turtle
 
G

Guest

KIM said:
I understand that access uses the first 15 rows of an imported excel sheet to
determine whether the access field is numerical or text. I have a worksheet
with a date column, and columns that contain both numbers and text entries
(in the form of less than values e.g.<1). Therefore the date column cannot
be changed to text or number otherwise it looses the correct format. And
although the numbers can be changed to text in excel they are only recognised
as numbers in access. I need all the data in the format i entered it i.e 12,
34, <1, <100, etc. It is not practical to invent a convention to try and
show the < values as a number. The only way I have found to get the all the
information across from excel into access without error values (e.g.#NUM!) is
to enter ' in front of all entries. I have done this manually (i.e. entering
each cell separately) but this is not practical for the large amount of data
I have. Is there any other way round the problem? If not is there any other
way of getting ' in front of every entry without having to do it manually? A
macro perhaps? If I do have ' in front of every entry can I still calculate
average values for the columns?

Thank you
Click to expand...
 
Top