Forum Discussion
Custom Auto-number field data type problems
Hi, Arnel is good at the technical part, so I won't comment on that.
I do want to correct a logic error, though, in the description of the problem.
ATD0001 is not a number. ATD0001 is a text string consisting of three alpha characters (letters of the alphabet) and four digits. Just because you, the human looking at it, "see" them as "numbers" doesn't make them numbers internally to the database engine.
For comparison, consider the following pseudo-equations.
1 + 1 = 2
A+ B = 22
A101 + B101 = 9999
The point I'm driving at is that only the first of the three makes any sense, because 1 is a digit symbolizing the value of one thing: one apple, one horseshoe, one parachute, one monkey.
2 is a digit symbolizing the value of two things: two apples, two horseshoes, two parachutes, two monkeys.
That's mathematics.
A + B = 22 makes no sense as stated. You can't add non-numbers. Of course, you could pretend that A and B are variables which represent underlying numerical values. In which case, if A is a variable whose current value is 18, then you can proceed to substitute in that value and determine that B must currently have the value of 4. Otherwise, without that additional layer of logic, it is a nonsense equation.
The third equation is closer to your so-called "autonumbers", where alpha characters and digital characters are concatenated. It should be obvious by now that A101 and B101 can't be used as numbers. The same is true for the ATD0001 and ATD0002 of your description.
What you CAN do, though, is add in that additional layer of logic mentioned above. You can separate out the two components, "ATD" and "0001" and when you do that, you can start to mathematical manipulate the number part as needed.
In general, when you want to DISPLAY certain values with a prefix in an Access relational database application, you can easily do it by creating two fields in the table. One field is a text field, the other is a number field. In this case, the text field can have as a value, "ATD" and whatever other values are appropriate. The number field does the heavy lifting of storing the sequential values required to uniquely identify things.
You can combine them into a TEXT string to DISPLAY it as a single string, e.g. "ATD0006", in a form or report.
Nobody will take this number add involved in computation.
the OP, which is the programmer knows that, and knows what he is doing.
I get that. I guess it's too subtle a point for some.
The StudentID itself DOES NOT INCLUDE the ATD as part of the saved value, which is unambiguously a Long Integer.
ATD is there for display only.
You yourself admit that in your next response.
"...because on each records you will have same info on one field that is "ATD", ..."
It clearly, unambiguously, is NOT part of the StudentID whether it's a separate field or merely a display only prefix.
I did suggest a separate field on the possibility that, at some point, there would be other, different prefixes needed. If the only value ever permitted for this three-letter prefix is the "ATD", then that reinforces once again. It's not part of the StudentID itself, it's a display only feature and otherwise unnecessary.
And finally, at the risk of offending the OP, let me quote WHY it's necessary to point out the obvious to him (and apparently to you).
" I do realize my "auto number" - "ATD"0000 - is not actually a number. I think the problem is that by default, Access labels the datatype of any auto number, regardless of whether it is a custom auto number with alpha characters or not, as a number. "That is an inaccurate assertion. The underlying AutoNumber IS a number. It is a Long Integer. Access did not "label" it as such. You should know that. You do know that!
Once more for the OP.
- AutoNumbers are Long Integers.
- It's possible to format them for display only with prefixes, suffixes, leading 0s and other adornments.
- The value in the Autonumber field itself is saved without any of those adornments.
- Access allows the display of the adornments as an aid to humans.