Software

Ask something
Discussion Locked

Access Data Types / Field Size

0 helpful
5 replies

by js6

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
A table needs to have an Account Number field. The account could possibly be up to 16 digits and as few as 2 digits. I would like to sort by the field. This field will be part of an imported table. Also, I would like to format the field with dashes / spaces similar to a credit card number: 1234-5678-8765-4321. Is Data Type Number and Field Size Double the best?

5 replies

by peyison

For a number that large, your best bet is to make the data type text. The problem is that even though a double can handle very large numbers, if the number is too big you start losing precision. In other words, the numbers will start to round and you'll lose the last digit or more. If I enter the number 12345678876543231 in a double field, it actually displays 1234567887654320.

The other benefit of using text is that you can use an input mask. With the double, you'd have to use the format property.

The problem with using text is the sorts won't work very well.

The list:

1
2
3
10
21

will sort like this if the field is text:

1
10
2
21
3

An option to fix this is to 0 fill the text:

01
02
03
10
21

Hope this helps.

by donq

Peyison is correct + you MUST use the "Text" data Type if you plan to format dashes and/or spaces into the presentation. Add 1 character for each dash and/or space etc. Toyr example is a Text Field Sized at 19 (min.).

This discussion is closed to new replies.