can i use a "-" in a number field?

[Daniel M]

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.

 

[Banana]

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.

 

[KARL DEWEY]

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.

 

[Jeff Boyce]

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