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. 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?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you

If you put an apostrophe in front of all of your entries, they become text.
AVERAGE ignores TEXT, so no, you won't be able to get an average.

As for as your real problem, you DO have a mix of numbers and text. "<1" is
not a number. You would have to adopt some convention for a true number that
indicates "less than 1", perhaps -.0001 or something similar. Then all of your
Excel entries can be represented as numbers and imported correctly into
Access.

On Tue, 22 Feb 2005 08:15:13 -0800, KIM wrote:

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. 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?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you

I think you can average a TEXT entry in excel as long at it has a number in -
I asked a question about 'averaging columns with less than (<) text entries'
and I have had replies letting me know the correct formula which seem to work
on entries with ' infront of it too. Unfortunately its is not practicle
adopt some convention for a true number that indicates "less than 1" because
I need the exact data. Also I have a large amount of data so it would take a
long time to go through and change all the less than signs plus not all the
figures are less than 1 so it could get all very confusion trying to remember
what the convention was.

I am assuming that i would therefore require a macro or something to put the
' infront of every entry?

Thank you for your help though

Kind regards

Kim

"Myrna Larson" wrote:

If you put an apostrophe in front of all of your entries, they become text.
AVERAGE ignores TEXT, so no, you won't be able to get an average.

As for as your real problem, you DO have a mix of numbers and text. "<1" is
not a number. You would have to adopt some convention for a true number that
indicates "less than 1", perhaps -.0001 or something similar. Then all of your
Excel entries can be represented as numbers and imported correctly into
Access.

On Tue, 22 Feb 2005 08:15:13 -0800, KIM wrote:

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. 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?
If I do have ' in front of every entry can I still calculate average values
for the columns?

Thank you