add value to cell according to another cell in same record

[kishanpatel50]

i have a table of articles. A field in the table is ArticleSubject

the ArticleID is made up of 3 letters then 3 numbers. i want the 3 letters to be something according to the subject
for example i want the first 3 letters of the ArticleID to be MAT*** (* is a number) if the subject is Maths
or ENG*** if the subject is English

the subject is picked from a listbox in the same record

how would i do this in a table . i am reluctant to use append or update queries.
but will do so if its the only way.

 

[SamDeMan]

users usually enter information through a form, do it on the form. tables should be used to store information, not the process them.

sam

 

[The_Doc_Man]

You have two choices. SamDeMan's answer is quite correct. Do the processing in the form.

First question: Are you entering, generating, or picking the numeric portion?

Second question: Do you validate based on the whole synthesized string or just on the alphabetic portion?

Answer these questions and we can give you more specific answers to solve your problem.

 

[kishanpatel50]

im going to be entering the number bit...ive decided to do the calculating bit in the form and then add the letters bit infront of the number in the table.

 

[neileg]

im going to be entering the number bit...ive decided to do the calculating bit in the form and then add the letters bit infront of the number in the table.

Usually, when you are composing a reference like this, it is better to store the individual components in their own fields and then add it all together only when you display it to the user. Because the components have meaning, you may want to test on the basis of those components and your life will be much easier if they are separate in the table.