Id

Hi,

Can I get a id field to equal the first character of their first name, 3 characters of a users surname follwed by 2 numeric characters.

The first bit I guess I would achieve by something like this

id1= lef(UserForm.txtsurname, 3)

but the second one I would like to be incremented but the number of people who share the surname. ie.

john williams = JWILL01
jane williams = JWILL02
Gary williams = GWILL01

so on.

Is this possible

Possible, but how do you determine which userid's already exist? Where are
they all stored?

Tim.

--
Tim Williams
Palo Alto, CA


"bach" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> Can I get a id field to equal the first character of their first name,
> 3 characters of a users surname follwed by 2 numeric characters.
>
> The first bit I guess I would achieve by something like this
>
> id1= lef(UserForm.txtsurname, 3)
>
> but the second one I would like to be incremented but the number of
> people who share the surname. ie.
>
> john williams = JWILL01
> jane williams = JWILL02
> Gary williams = GWILL01
>
> so on.
>
> Is this possible
>
>
> --
> bach
> ------------------------------------------------------------------------
> bach's Profile:
http://www.excelforum.com/member.php...o&userid=26134
> View this thread: http://www.excelforum.com/showthread...hreadid=468946
>

Just a note:
Following the description you gave, Gary Williams should have an id
of GWILL03, not GWILL01.

In order to achieve this task you would have to provide a way
to get the count of the surname from your db. I would do it
something like:

Dim Init1 as string
Dim Init2 as string
Dim FName as string
Dim LName as string
Dim I as Integer

Init1 = Left(UserForm.txtfname.text,1)
LName =UserForm.txtsurname.text
Init2 = Left(LName,3)
'This assumes that only 99 people can have the same last name
I = Format(Countif(WorksheetName.Range(LastNames),=LName),"00")
id1=Init1 & Init2 & I

'Lookup

"bach" wrote:

>
> Hi,
>
> Can I get a id field to equal the first character of their first name,
> 3 characters of a users surname follwed by 2 numeric characters.
>
> The first bit I guess I would achieve by something like this
>
> id1= lef(UserForm.txtsurname, 3)
>
> but the second one I would like to be incremented but the number of
> people who share the surname. ie.
>
> john williams = JWILL01
> jane williams = JWILL02
> Gary williams = GWILL01
>
> so on.
>
> Is this possible
>
>
> --
> bach
> ------------------------------------------------------------------------
> bach's Profile: http://www.excelforum.com/member.php...o&userid=26134
> View this thread: http://www.excelforum.com/showthread...hreadid=468946
>
>

My fault,

GWILL should have 001 as he has a different first name from the JWill and so he is unique. The number is for if a user has the same first letter and last three letters for their surname they can have a number to distinguish between them.

All members are stored in the spread sheet with all their details.

In your first post you asked for a "2 character numeric" at the end and you
wanted it "incremented by the number of people who have the same surname"
That would result in GWILL03 NOT GWILL001

So, do you want a numeric with 2 characters or 3 characters?

Is it incremented by surname or by surname plus firstname?

Try to be accurate in what you ask for.
Thank you

"bach" wrote:

>
> My fault,
>
> GWILL should have 001 as he has a different first name from the JWill
> and so he is unique. The number is for if a user has the same first
> letter and last three letters for their surname they can have a number
> to distinguish between them.
>
> All members are stored in the spread sheet with all their details.
>
>
> --
> bach
> ------------------------------------------------------------------------
> bach's Profile: http://www.excelforum.com/member.php...o&userid=26134
> View this thread: http://www.excelforum.com/showthread...hreadid=468946
>
>

I want the numbers at the end of the name to increment.

The idea of the numbers is that if their are two or more members with the same First letter of first name and surname i.e. B Jones (ID = BJon), the numbers will be used to make a unique id. So if there were 2 ben jones the first one would have 001 and the second would have 002, creating

BJON001
BJON002
CJON001 - as it has a different first letter and is the first instance of the name so 001.

Is that any better

Cush,

Could you explain this code, I know it was an example but I dont understand it.

I = Format(Countif(WorksheetName.Range(LastNames),=LNa me),"00")

the Range(lastnames) would need to be a colum (range(b:b) is that right ?)

What is the =lna me),"00")

Dont get the last bit.

Bach