Generating account numbers

[Parkroyal]

We use an account number that consists of 3 different elements.

1)A four leter code that indicates the type of account (these are finite and so are listed in their own table)
2)A 3 digit number (has leading zero's)
3)A text code that is used as a booking code (this is a code generated from the clients trading name and so is generated from contact details).

I would like too know if anyone can tell me a way of generating the 3 digit number. I need to be able to use leading zero's so I don't think I can use autonumber but this generation dose need to be automatic with no duplicates.

I then need a way of bringing all three of these elements together and storing the result in the client account table.

Can anyone help? I did see an article somewher on autonumber without autonumber but I can't find it now.

Thanks

 

[BNPMike]

What is the rule about the three digit number? Can numbers be re-used for different Account types and for different booking codes? I assume so otherwise after 1000 goes you will have run out of numbers.

I think you are going to have to do a select to find the highest 3 digit number where Fourlettercode = XXXX and textcode = YYYYYY. Then issue the next number.

 

[Parkroyal]

Thanks

Yes the numbers can be re-used for different account types, that gives me a total of 999 accounts for each type, 8000 possible over half of them corporate (some of our large corporate's have .............. at an avg..... I've just run the numbers, if I ever reach even half capacity I'm off to the Sun ---- 4 good and it's someone else’s prob.

I would change this system but we have hundreds of accounts already registered so I would have to issue each account with a new number --- Nightmare!

So, If I've caught your train of thought ... list number as text (to allow the leading zero's) run a query to find highest number for account type and add 1. Ok I can live with that 'cause I can fire the query in the background on an event linked to the list for picking the account type.

But I still then need to combine the result of the account type, this query and the booking code then enter all that as one field in a table. How would I achieve this?

Pete

 

[BNPMike]

Lets say your ids are like BARG078ORACPA. You can do a select that picks out the 078 substring, converts it to a number and then you get the MAX of it. Your new number is 79 so you create a string BARG+079+ORACPA. What function converts number to text with leading zeros, you'll have to look up because Access help doesn't make it easy fo me to spot quickly.

 

[Parkroyal]

Yes thanks I think I get the idea.

Thanks for your help. I'll go and read up in help now I know what to look for

Cheers