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

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

Tim.

--
Tim Williams
Palo Alto, CA


"bach" wrote in message
...

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),=LNa me),"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.


--
bach
------------------------------------------------------------------------
bach's Profile: http://www.excelforum.com/member.php...o&userid=26134
View this thread: http://www.excelforum.com/showthread...hreadid=468946

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 wit
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 wer
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 instanc
of the name so 001.

Is that any bette

--
bac
-----------------------------------------------------------------------
bach's Profile: http://www.excelforum.com/member.php...fo&userid=2613
View this thread: http://www.excelforum.com/showthread.php?threadid=46894

Cush,

Could you explain this code, I know it was an example but I don
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 righ
?)

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

Dont get the last bit.

Bac

--
bac
-----------------------------------------------------------------------
bach's Profile: http://www.excelforum.com/member.php...fo&userid=2613
View this thread: http://www.excelforum.com/showthread.php?threadid=46894