Thank you sir...I used a similar routine as to the one you posted below...

However, the only thing I added, was that I put the account creating in a
Trigger so that it would
be written to the same record as they were added into the database!

Thanks again so much for your help and the help of others in this list.

I find this list a very helpful.

Best regards.

-----Original Message-----
From: Roland Boorman [mailto:r_boorman@y...]
Sent: Thursday, December 14, 2000 7:26 PM
To: sql language
Subject: [sql_language] Re: Creating an account number


lets start with resolving your leading zero problem.

first select your region and your directorid.
I ssume that this is not a problem

simple add 10000 to the Directorid

  thus id 1 becomes 10001

now convert to text

then  Region + right(10001,4)
this will give you say NX0001

then of course you update directors table where
director = direcorid


--- Sean Deegan <sean@d...> wrote:
> Hello all, this may be an easy question, but I feel
> like I've been
> slamming my head against a wall...
>
> I have the beginnings of an app with a couple of
> tables...
>
> Regions, Directors
>
> I have three columns in the directors table that I'm
> concerned with.
>
> Region_ID (FK to the Regions tables), Director_ID
> and AccountNO...
>
> Here's what I want to do, I want to create an
> account number using the
> region name (ie NE, SE, MW etc...) and a 4 digit
> number using the
> diretor_ID.  I've managed to use the convert
> function to do a select
> statement which add's a specified amount of 0's
> between the region name
> and the account number (but it's not dynamic...in
> other words, i need it
> to remove a 0 when I go from 9-10 and from 99-100
> etc...).
>
> Now, the trickey part (or not), I want to write this
> new number to the
> AccountNo field in the Director table...any
> suggestions would be
> helpful...
>
> BTW, the Director_ID is the primary key for the
> Director table, and is
> generated as directors are added.
>
> I know this is not a whole lot to go on, I'm just
> hoping someone out there
> has done this before and can get me going down the
> right road before I
> continue to hit dead ends....
>
> Thanks in advance!
>

---
You are currently subscribed to sql_language as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-sql_language-$subst('Recip.MemberIDChar')@p2p.wrox.com