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.
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.