Autonumber using partial text

[gbutler288]

Hi I need to create an access database that will take something in this format P0H003810HL001 then with the next entry add HL002 and so forth but it has to stop at 999.



I want to be able to have a user input a code say the middle of this text/number 381. Do a search for the next available number with the code of 381 assign that number to a user's information.

So if I say I need a new identification code for dept. 381 it would go to the 381 table and assign the next ID code P0H003810HL002.

Can access do this? If so is there a tutorial you could point me to?

Thank you for your help.

[Robeen]

It is not totally clear what exactly you need to do - but from what I read - the answer is that it is certainly possible to achieve what you want in Access.

Incrementing the last three digits of your Alpha-Numeric value is pretty easy to do.
Locating the 381 in the middle of your Alpha-Numeric value is pretty easy to do.

Can you explain in a little more detail what you need to do?

[gbutler288]

I have a unique name as I posted above that I need to assign from 001 to 999. I have multiple department codes that are located in the middle of that unique name. i'm wanting to create a database where you can input a dept code and the user's name and it will generate what would be a unique computer name with the dept id like the following P01002280HLXXX .Anyone in dept 228 would get a new pc name from 001 - 999 as I received new hires. I have multiple dept codes. I'm guessing each dept code would be its own table that would provide the names. I'm not sure how to get access to auto generate that unique name #1 and #2 how to get access to pull the table from the dept. id located in the middle of that unique name? Oh yes...and I'm new to this. I'm a network admin not a db guy but guess who gets the DB job this week. smiles widely

[Robeen]

I think I understand what you need to do but let me repeat it so I'm sure I've got it correct.

First, I'm assuming you already have a list of unique computer names for each of the existing employees in each of the existing departments.

Second, I'm assuming that the Department Code is always in the same position in each unique computer name.
For example - the unique name you posted is 'P0H003810HL002'.
The department in that name starts at position 6 of the name and has a length of 3.
Will this always be the case?

Third, I think you want an input form into which you can type in a new employee's name and the employee's dept code - and have Access automatically assign the new employee's computer the next unique name [with the 3-digit number at the end of the unique name incremented] - AND the correct deptID in the middle of the unique name.

Q. Is the 'P0100' at the beginning of the unique name always going to be the same? So that only the DeptID and the 3-digit number at the end of the unique name are changed every time?

[gbutler288]

I think you have it. The portion of the name would only change on the last 3 digits for each dept. code. However I have many dept codes. So I would have a range of 001 - 999 for each dept. code.

So if Judy works in dept 381 I want access to give me the next available PC name for dept 381 which would look like P01003810HLXXX (x's being the number) but if Tom starts in dept 219 the same thing for him.

I think we are on the same page.