Why not let the datatype actually change when you choose to change

[tweaksource]

When an Excel spreadsheet is imported to Access, the data type goes with it.
This is fine.
The data type should be correct before it is imported, I agree.

My question is if, for whatever reason it isn't, why not let the user
actually be able to change the data type and allow the existing data to
remain? Would that be so bad?
When you import a column with "General" type from Excel into an Access
field that is "Number", if the original data had decimals (like 2.5) they are
lost.

Fine, but why, after the data type in access has been changed to Number,
Format--> Fixed, Places-->2 can I still not enter a freaking decimal and get
it to stay?

WHY?!?!?!?!?!?!?

WHYYYYYYYYYYYYYYYYYYYY???????!!!!!!!!!!!!!!!!!!!!!!!!!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc83-74bd5ac01bc5&dg=microsoft.public.access

 

[John W. Vinson]

When an Excel spreadsheet is imported to Access, the data type goes with it.
This is fine.
The data type should be correct before it is imported, I agree.

My question is if, for whatever reason it isn't, why not let the user
actually be able to change the data type and allow the existing data to
remain? Would that be so bad?
When you import a column with "General" type from Excel into an Access
field that is "Number", if the original data had decimals (like 2.5) they are
lost.

Fine, but why, after the data type in access has been changed to Number,
Format--> Fixed, Places-->2 can I still not enter a freaking decimal and get
it to stay?

WHY?!?!?!?!?!?!?

WHYYYYYYYYYYYYYYYYYYYY???????!!!!!!!!!!!!!!!!!!!!!!!!!

The rationale, as I understand it, is that Access Fields have strong, stable
datatypes; Excel cells DON'T have a defined datatype. On import Access must
GUESS (based on the first few records) what datatype is intended. If the first
ten rows of a spreadsheet have integer numbers, Access will guess (probably
incorrectly) that all the cells in that column do.

One getaround is to create a new, empty table; define all the datatypes when
you create the table. Then use File... Get External Data... Link to connect to
the spreadsheet and run an Append query to populate the table.

It's a hassle, and an extra step or two, but it's the only way I know to get
around this problem. I fully agree that something resembling the datatype
specification in the Text Import definition would be very helpful in importing
Excel!

 

[Albert D. Kallal]

Fine, but why, after the data type in access has been changed to Number,
Format--> Fixed, Places-->2 can I still not enter a freaking decimal and
get
it to stay?

WHY?!?!?!?!?!?!?

WHYYYYYYYYYYYYYYYYYYYY???????!!!!!!!!!!!!!!!!!!!!!!!!!

because your column is a integer data type. It does NOT matter if you set
the decimals to 2 places. Try to remember your grade school math! Integers
can't store a decimal value. Changing the format to 2 decimal places DOES
NOT change the fact that you have a integer column.

If you need to have decimal places allowed in the column, you have to choose
a data type that allows decimals. You can use single, double, or likely for
currency/dollar amounts you want to choose the currency data type for that
column.

so, I repeat:
Setting the decimal places to "2" for a integer data type DOES NOT give you
the ability to enter decimal values. You have to change the number type to
something where it is LEGAL to enter decimal values, and THEN you can set
the number of decimals places to 2 or 4 or whatever...

 

[Banana]

I totally agree with Albert. However, I should probably point out that
the fact that Access UI does not make it apparent may be playing a role
here. In Excel, there's no such thing as "Integer", while Access UI
represents it as Number, but one has to look at the options on the
bottom of table designer to choose the appropriate sub-type of the
Number, which are as you just listed.

For someone coming from Excel, I can see why they would think changing
decimal places makes sense even though in world of database where we
have strong-typed datatypes that does not just fly especially when we've
chosen integer types rather than float or decimal types.

 

[Albert D. Kallal]

I totally agree with Albert. However, I should probably point out that the
fact that Access UI does not make it apparent may be playing a role here.
In Excel, there's no such thing as "Integer", while Access UI represents it
as Number, but one has to look at the options on the bottom of table
designer to choose the appropriate sub-type of the Number, which are as you
just listed.

For someone coming from Excel, I can see why they would think changing
decimal places makes sense even though in world of database where we have
strong-typed datatypes that does not just fly especially when we've chosen
integer types rather than float or decimal types.

A good point. However, looking at questions even in the access group and NOT
being related to Excel, this question comes up a LOT. In other words, a user
creates a column of a integer type (non real such as Long, integer,
Boolean). They then go into the table view (or property sheet of the text
box on a form), and then set the decimals to 2, or some such. this happens
OFTEN!!!

In other words, I don't really blame this issue much on excel, but simply
that OFTEN people create the wrong type of column here.

Perhaps the solution here is a message that warns decimal places DOES NOT
make sense when the column is of a integer date type. However you can't
remove the decimal formatting ability, since sometimes we do want the .00 to
display...even when it not possible to have real values as opposed to
integer values.

I don't have a great solution to this problem, but this issue comes up a lot
even for access only people. I really don't blame the wonderful flexibility
that excel has for this issue, but no doubt having using Excel it does make
this mistake (issue) even more common....

 

[Banana]

Perhaps the solution here is a message that warns decimal places DOES NOT
make sense when the column is of a integer date type. However you can't
remove the decimal formatting ability, since sometimes we do want the .00 to
display...even when it not possible to have real values as opposed to
integer values.

Just thinking out loud. Wouldn't it make more sense to disable the
Decimal property if integer data types were selected. This should send a
message to the user that this may not be what they want if they want
fractions. They still can use Format property if they want, but at least
it'd be a hint.

I don't have a great solution to this problem, but this issue comes up a lot
even for access only people. I really don't blame the wonderful flexibility
that excel has for this issue, but no doubt having using Excel it does make
this mistake (issue) even more common....

Agreed. Someone mentioned to me that it's far more common for people to
go about with their applications never even knowing that Number actually
is not a data type in Access; it's merely a class of several different
numeric data types of which could be either an integer, a real or a
decimal.

 

[David W. Fenton]

Access Fields have strong, stable
datatypes; Excel cells DON'T have a defined datatype. On import
Access must GUESS (based on the first few records) what datatype
is intended. If the first ten rows of a spreadsheet have integer
numbers, Access will guess (probably incorrectly) that all the
cells in that column do.

One getaround is to create a new, empty table; define all the
datatypes when you create the table. Then use File... Get External
Data... Link to connect to the spreadsheet and run an Append query
to populate the table.

It's a hassle, and an extra step or two, but it's the only way I
know to get around this problem.

It's not really all that much of a hassle if it's a recurring
process. Indeed, if you're putting this in an app so that there can
be regular imports, it would be foolish *not* to do it this way.

I fully agree that something resembling the datatype
specification in the Text Import definition would be very helpful
in importing Excel!

I think one of the issues is that people have never learned how to
declare data types in their spreadsheets. Back in Lotus 123, which I
learned in 1985, BTW, you had to use the ' and " to enter data so
that it would be stored properly. Excel guesses for you, and gets it
right 99% of the time, but it doesn't *store* it with its guess, so
Access (which doesn't include Excel's "guessing" logic) doesn't have
much to go on, so it guesses as best it can.

Sure, an import spec would be helpful, but if it weren't for what I
consider to be a missing feature of Excel, it wouldn't be an issue
in the first place. Indeed, it occurs to me that likely the Access
spreadsheet import routines were originally engineered back in the
day when the spreadsheets *did* have the ' and " in the cells --
it's easy to forget how much things have changed since Access was
created.

 

[David W. Fenton]

Perhaps the solution here is a message that warns decimal places
DOES NOT make sense when the column is of a integer date type.
However you can't remove the decimal formatting ability, since
sometimes we do want the .00 to display...even when it not
possible to have real values as opposed to integer values.

Hmm. Wouldn't that be controllable with the FORMAT property of your
controls?

I dunno. I've always rather mistrusted the decimal places property
in table design (and on controls) because I don't fully understand
its interaction with formats. Input masks also interact with formats
in some unexpected ways, and all of these seem to me to be areas in
which Access has a feeling of being messy/unfinished. None of this
has changed since my earliest use of Access (version 2, 1996), but I
have never been happy with it.