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.
- George, it's a Student ID (Identification).
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.
- George,
I really appreciate your reply. 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. My next step, if I am not able to make this work using my custom auto number of "ATD"0000, was going to be just as you recommend and create separate fields for the alpha characters "ATD" and the auto number. My ultimate goal is to, as you also mentioned, have the alpha characters and the auto number display together as a single string in reports. Ultimately, if I am not able to have my custom "auto number" display as a single string in my tables and forms, I will resort to separating them, as you recommend.
Thanks again for your response. Every recommendation is a help at this point.Again, any value saved into a table with alpha characters in it is "Text", not numbers.
AutoNumbers are a special kind of datatype, used in Access to generate unique numbers of Long Integer datatype.
The difference is that Access automatically generates those new values for the long integer at the time a new record is started in that table. In all other ways, they are Long Integers.
You simply can not create an AutoNumber with letters in it. That's not what is happening.
What you are doing is FORMATTING FOR DISPLAY purposes.
You can format most types of values in Access to display them as you want.
Don't confuse the DISPLAY with the STORED value.
Consider this quick and dirty test, for example. Selecting a value reveals ONLY the actual value without the display only prefix added by the format.
