losing decimal when loading to MS access

[Twinmammy]

I'm trying to load some data to a table in access from excel. When I load it to an existing table (field settings are set at Decimal auto decimal places), I lose the decimal and the numbers after! any advice?

Thanks

 

[Twinmammy]

also, if I open the table in access and try to type 1.1 the .1 immediately dissappears!

 

[CindyK]

What is the data type of the field the numbers are going into? My guess is that it's an integer or long integer! If so, change it to double or single and mark how many decimals you want!

 

[Twinmammy]

Thanks Cindyk, I have it set to Double but I'm still having issues! It now seems to be where I have two decimals like 10.2.1. Can this be done as a number or would it have to be text?

 

[CindyK]

What does your data look like in Excel? If you have a cell value of 10.2.1, what do you want that to be once you move it to Access?

 

[dhookom]

You can't have two periods in a numeric field. If you have more than one period, I expect the value isn't necessarily numeric. Phone numbers and zipcodes are generally stored in text fields.

If you really need to use numeric with two periods, you may need to use more than one field.

Duane
Hook'D on Access
MS Access MVP

 

[Twinmammy]

Thanks all, its a reference number for some actions. I create reports out of access by popping data back into excel in a different formay (I receive many files monthly that need to be collated into a database and then reported)

But in excell its ordering the actions in the pivot tables like

1.1
1.2
10.3
2.1
3.1

Instead of in numerical order like

1.1
1.2
2.1
3.1
10.3

I've tried to order them ascending but I think as they are being read as text rather than numerical it wont work

 

[CindyK]

Your examples don't include a cell with more than one period, but it sounds like you also have things like 2.1.1, 2.1.2. As Duane states, that's not going to turn into a valid number in Access no matter what. BUT - you are implying that your reason for exporting the data to Access is ONLY for sorting options? Can you change the underlying structure to always use 2 (or 3, if necessary, digit "numbers", and then a text sort would work. For example - 02.01.01 and 02.01.02

You could use formulas in Excel to do a batch replacement in the data, and then use the text field in your pivot tables.

 

[SkipVought]

two decimals like 10.2.1

What you have is TEXT!

This is an IDENTIFIER, not a number.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

As I stated, you may want to separate each numeric value into its own column/field. Then, you sorting will work correctly.

Duane
Hook'D on Access
MS Access MVP