Access Data types

[Obalola]

I have a MS Excel dataset and of one of the columns contain both number and text data types. Is there a MS Access data type that accepts number and text formats as i will like to create database table using the excel dataset?

Please note that MS Access version is Access 97.

 

[xlbo]

Your nest bet is to use a text datatype (char or varchar depending on what is available)

Numebrs will convert to text but text will not convert to a number

This will mean however that you will have to be careful if you are planning to do any calculations based ont eh "numbers" that are stored

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

hi,

I have a MS Excel dataset ...

I agree with Geoff.

Do not make the mistake that formatting your column of mixed text and numbers will solve your problem.
[red]
Formatting changes NOTHING!
[/red]
You must actually CHANGE the numeric values to TEXT. You can do that by PREFIXING an APOSTROPHY to each number. For instance, if your column is A, starting in row 2, put your formula in an empty column [that column]...
[tt]
[that column2]:=IF(ISNUMBER(A2),"'","")&A2
[/tt]
copy the formula down in that column.

COPY that column

SELECT column A

Edit > Paste Special -- VALUES

DELETE that column


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

I'm forced to do this quite a bit with data I have at my job.
I've found it works just as well if you simply select the column in your spreadsheet and format it as text.
(I use Access 2003).


Randy

 

[Obalola]

thanks everyone for your insighful and invaluable contribution.