Table design - Data type for numbers that aren't used in calculations

[DonR]

I have several tables with ticket numbers. These aren't used in calculations and they are unique and they are always numerical never any other characters. I've always used a number data type for them to make sorting easier but I've read some posts on forums that say to never use a number data type if calculations aren't going to be performed. To use a text data type instead. However, nobody ever gives the reason for doing this. Does anyone have suggestions or the reason to use text vs. number? I've been redoing some of my old databases and can sort using the val function but was just wondering what the reason for not using a number type is. Also, when using the text type I have to limit what the user can input to avoid getting any characters other than 0-9.

[ranman256]

If it's a number just make it a number. It wont cost nuthin and you may need it later.

[rpeare]

There's no good reason to use a non numeric data type for a numeric field that I can think of.

[CJ_London]

I've read some posts on forums that say to never use a number data type if calculations aren't going to be performed.

are you sure they say never? or doesn't matter?

If you are sorting or the field is to be indexed, use numbers, they are faster than text

only reason I can think of for using text datatype for a number is if the number is simply too big to be treated as a number, or preceding zeros matter for some reason, not just for display purposes. (for display you would use the format function to include the preceding zeros)

[Micron]

never use a number data type if calculations aren't going to be performed.

That sounds a bit absurd. Perhaps there is some context around that statement that you're not providing here?

Short text field size can be considered dynamic if I understand the M$ documentation on data types. It's used space is equivalent to what is required to hold actual values. The field size limits only govern the maximum number of characters that can be stored. So maybe you can save some space over a few hundred K or millions of records. On the other hand, if you use numeric types of byte, integer or long (1, 2 and 4 bytes) you might be using 4 bytes (long) when storing 2 when you could use far less by storing 2 as text.
However as far as I'm concerned the payoff with using numbers is that they follow logical order (otherwise text 111 comes before text 2) and you may decide to sum/average with them later on. Cannot do that with numeric values that are text. Trying to order numbers as text is a not too uncommon problem posted in this forum.

[CJ_London]

you might be using 4 bytes (long) when storing 2 when you could use far less by storing 2 as text.

actually you wouldn't assuming you are using unicode which is pretty much the standard text takes 2 bytes for string length plus 2 byte per character so '2' takes 4 bytes

[Micron]

actually you wouldn't assuming you are using unicode which is pretty much the standard text takes 2 bytes for string length plus 2 byte per character so '2' takes 4 bytes

Then that ought to mean that number fields can be smaller than text because 222 (long) ought to be 4 bytes; 222 (text) 8 bytes, according to your statement. However, in researching unicode, I find that it depends on the unicode spec, which seems to be that currently, it is anywhere from 1 to 4 bytes. So perhaps it can be worse than what you seem to be suggesting.

If all of that makes any sense, then any number stored as text quite possibly takes more disk space than most/many numbers. If that is true, then it certainly doesn't support saving disk space as a reason for storing numbers as text. That means the initial notion of 'always store numbers as text if you're not going to do math on them' makes even less sense.

[DonR]

I know I saw the comments in some forums that I looked at. Can't find them right now but here's something from a book talking about using text fields for numbers not used in calculations. https://books.google.com/books?id=W8...umbers&f=false

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications. I'm going to delete the duplicate moderated post.

Welcome to the site by the way!

[DonR]

Thanks - I'm working on redoing some old databases that I setup 16 years ago. In 2 different tables I have 3 different tickets so 3 fields. All of the tickets have preprinted numbers on them so in my old setup I used "number" as the data type. Trying to decide if that was best or if they should be short text since no calculations will be performed on them. The only thing I do need is to be able to sort them on forms and reports.

[Micron]

I agree with the sentiment there that phone numbers typically should be text, and probably things like part numbers as well. However, based on my prior post, the notion that you're saving disk space by doing so seems erroneous. As for "The only thing I do need is to be able to sort them on forms and reports" I'd ask how you plan to make 11 come somewhere after 9? You do understand the question (and how textual numbers sort)?

1, 2, 3, 4, seems easy, right? But when you get to 2 and 3 placeholders, it's
1, 11, 111, 12, 121, 122, 13, 14, 2, 22, 3...

[CJ_London]

with reference to your link, I would store a phone number as text because it has a preceding zero which is typically required when dialling a number and to format a number with preceding zeros can be difficult because it depends on each and every number having the same number of digits.

In the uk we might have a number 01211231234 (10 digits and a preceding zero). another number might 0150923456 which has 9 digits and a preceding zero. If you stored as numbers and formatted for 11 chars you would have 00150923456 which is clearly wrong.

I don't know about zip codes but if they can have meaningful preceding zeros aka a telephone number then potentially the same issue exists. The same could apply to part numbers. So the examples are broadly valid. However a bank account number, which most likely has preceding zero's I would store as a number because bank account numbers are always 8 digits long. - However no doubt someone can prove me wrong on that point

The telling comment is 'you can improve performance if you allocate the smallest fields size possible'. Totally agree - and the smallest field size in terms of text v numbers - is numbers. The author is correct with the comment 'Access allocates disk space dynamically' but is only sort of correct with the comment 'a large field size doesn't use hard disk space'. What it means is 'regardless of the the field size specified, access only uses the space taken up by the actual characters' - i.e. a 50 char field populated with a 10 char value will take up the space required for 10 chars, not 50.

[Micron]

However no doubt someone can prove me wrong on that point

Mine is 7, not including the branch number, in which case it would be 11. I could post it here, but then I'd have to kill everyone, not just you!
Anyway, I think your last sentence says the same thing I said in post 5 - but you said it better.

[DonR]

Using 9 and 11 as text and using Val(fieldname) I can sort. Works in a query but I'm not sure about code.

Thanks to all who have responded. I have changed my fields back to numbers instead of text - this is how I set them up 16 years ago. I'm just trying to get some code into my database since everything I did before was with queries and macros. Since I'm new to writing code I've been searching for the best ways to do things (or how to do things) and ran across the comments I'd seen elsewhere about numbers/text.

[Micron]

Works in a query but I'm not sure about code.

I can't recall every attempting a vba sort. However, you did specify report sorting, and my thinking was that a report would sort independently of the calculated field in a query so I had to test, but that's not the case. Sorts fine.
Seems you have this wrapped up, so don't forget to mark as solved so that others don't read all the way through just to find it's solved.
Thanks.