If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
can i use a "-" in a number field?
I have a table of data that i want to keep a number field but the serial
numbers i am recording are 02-001234, 02-001235. Currently we just use the last few digits but we will be adding 03- and 04- numbers soon. Is there anyway to keep the - and put it in a number field? Excel can handle sorting numbers this way. Thanks. |
#2
|
|||
|
|||
can i use a "-" in a number field?
By definition, numeric data types can only hold numeric values and not
any texts. This is due to how it is actually stored, represented by binary equivalent of the numbers so there can't be any room for any other ASCII characters. If you want to use dash, you have few options. 1) Use Text. This will work OK as long you don't need to do calculations on it. 2) Use Format() to add the dash into a numeric field. This works OK as long your format is very consistent. If it could change or vary, it could get ugly fast. 3) Split into two column and store each component separately. This is the same principle as splitting first and last names and then using queries to concatenate the names in whatever format we want. Daniel M wrote: I have a table of data that i want to keep a number field but the serial numbers i am recording are 02-001234, 02-001235. Currently we just use the last few digits but we will be adding 03- and 04- numbers soon. Is there anyway to keep the - and put it in a number field? Excel can handle sorting numbers this way. Thanks. |
#3
|
|||
|
|||
can i use a "-" in a number field?
Is there anyway to keep the - and put it in a number field?
No, only in a text field. Numbers also do not have leading zeros. Excel can handle sorting numbers this way. No Excel does not. They are text fields. Try adding two of the Excel cells together that have that kind of data. -- Build a little, test a little. "Daniel M" wrote: I have a table of data that i want to keep a number field but the serial numbers i am recording are 02-001234, 02-001235. Currently we just use the last few digits but we will be adding 03- and 04- numbers soon. Is there anyway to keep the - and put it in a number field? Excel can handle sorting numbers this way. Thanks. |
#4
|
|||
|
|||
can i use a "-" in a number field?
Daniel
This may seem like nit-picking, but is critical to your solution ... Will you be adding, subtracting, multiplying or dividing any of those "serial numbers"? (I thought not!) Those may look like numbers but they are really "codes", aren't they? So you would NOT use a number field to store them, you'd use a character (text) field. If you need to sort them in a query, perhaps you need to use TWO fields to hold those "serial numbers", so you can sort each component (before and after the "-"). At a minimum, maybe you need to use Left(), Mid() and InStr() functions to calculate those two components before sorting by them. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Daniel M" wrote in message ... I have a table of data that i want to keep a number field but the serial numbers i am recording are 02-001234, 02-001235. Currently we just use the last few digits but we will be adding 03- and 04- numbers soon. Is there anyway to keep the - and put it in a number field? Excel can handle sorting numbers this way. Thanks. |
Thread Tools | |
Display Modes | |
|
|