Custom Auto-number field data type problems

[Mprovost]

I am just learning how to navigate Access and I am building a database which contains a 'Students" table with a custom auto number field, "Student ID".  The custom number is the prefix "ATD" with a four-digit number.  So the first auto numbered record in my table is ATD0001, the 2nd is ATD0002, and so on.  This is working just fine in this table.

In a different table, the "Student Grades" table, I created a look-up wizard for a "Student ID" field that utilizes the "Student ID" auto numbered field in the "Students" table as the drop-down menu source.  I also created a "Student Grades" form based on the "Student Grades" table.

The problem is that the data type for the "Student ID" auto number field in the "Students" table is "number" because it's an "auto number" I guess, even though it contains letters.  The datatype for the "Student ID" look-up field in the "Student Grades" table is also "number" because it is looking up information from a "number" datatype field.

When the drop-down field in my "Student Grades" form that is based on my "Student Grades" table looks up information from the auto number "Student ID" field in the "Students" table, I get this message "The value you entered isn't valid for this field".  It's looking for a number and the "numbers" in my auto number field are alpha characters and numbers combined.  I understand why it isn't working.  I'm just not quite sure how to fix this problem or if it is even possible to fix it.

As I said previously, I am new to creating Access databases.  I don't know how to code, and I pretty much know only basic stuff about building an Access database.  If anyone responds with a solution, please keep this in mind and keep the language and instructions very basic.

Thanks in advance to anyone who can help.

[#2]

@Mprovost 

is your custom "Student ID" field really an Autonumber (long) field and just prefixing it with "ATD" on your form.
on your "Student Grade" table, you need to make the "Student ID" as Long (numeric).
then for the lookup, create an Expression on the Query.

see this demo. open each table in design view and see the Datatype of Student id and 

the Lookup query.

open Student Grade table and enter some data on Student ID.

as a programmer you know exactly what is the purpose of this field, don't get astrayed.

[#3]

@Mprovost 

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.

[#4]

@arnel_gp 

Thank you for responding to my post. I compared the tables in your demo database to the tables in my database and all the Properties are the same. So I don't think there is a problem with either of the tables. I think the problem is somewhere in my form.
You suggested "for the lookup, create an Expression on the Query". If you're referring to the properties for my form, I will need more guidance, as you did not include a form in your demo. Should I use the Expression Builder somehow in the Property Table of my form or build my own. Is this even what you're referring to? So sorry for the lack of understanding, but as I mentioned, I am REALLY new at this. I have attached a screen shot of my form in the design view with the Properties Table open. Any further help would be appreciated.

[#5]

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.

[#6]

@Mprovost 

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. 

[#7]

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.

[#8]

@Mprovost 

if you will open the "Student Grade" table in design view, click on student id, and on the Lookup
you will see the Expression i made.

also i created a query based on this table and join with Student table to show the student name.

next created a form from this query.

if you will follow the same SQL in your form then you will have success on it.

you don't really need to separated this Student id into two fields?

because on each records you will have same info on one field that is "ATD", which will break first Normalization rule.

also this is Identifiier, so unlikely it will involved in any mathematical computation.

[#9]

@arnel_gp 

Thank you soooo much for your help, the demo database,  and explaining everything in plain language that's easy to understand. I will let you know how it works! 🤞🏻:smiling_face_with_smiling_eyes:

[#10]

@arnel_gp 

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.

1. AutoNumbers are Long Integers.
2. It's possible to format them for display only with prefixes, suffixes, leading 0s and other adornments.
3. The value in the Autonumber field itself is saved without any of those adornments. 
4. Access allows the display of the adornments as an aid to humans.