Generating a Client Number

[TomH]

This seems to be beyond my skill set. I'm attempting to auto generate a client number based on the following criteria:

First letter of first name

Last letter of last name

M or F for gender

Two digit month of birth

Two digit date of birth

Last three digits of birth year

Last four digits of SSN

The fields I have are:

NameFirst

NameLast

Gender

DOB (date formatted XX/XX/XXXX)

SSN

ClientNumber (the field I need calculated)

If the client information is

Sally Jones ,05/19/1965, XXX-XX-1234

the client number is:

SSF05199651234

Is there any way to do this in a calculation?

Tom

[comment]

This is very easy to do - but utterly worthless for any purpose I can think of:

Left ( NameFirst ; 1 ) & 

Left ( NameLast ; 1 ) &

Gender &

SerialIncrement ( "0000000" ; 100000 * Month ( DOB ) + 1000 * Day ( DOB ) + Mod ( Year ( DOB ) ; 1000 ) ) &

Right ( SSN ; 4 )

[Lee Smith]

I agree that this information would have limited use.

There is an error in the LastName caluculation, the OP wanted Right one Charater, and Upper.

I amended your calc

Left ( FirstName ; 1 ) & 

Upper (Right ( LastName ; 1 ) ) &

Gender &

SerialIncrement ( "0000000" ; 100000 * Month ( DOB ) + 1000 * Day ( DOB ) + Mod ( Year ( DOB ) ; 1000 ) ) &

Right ( SSN ; 4 )

HTH

Lee

Edited July 2, 2009 by Guest

[TomH]

Thank-you for your quick replies, I see where I was going wrong now.

I agree that this is a nonsense number, however it is required by the state. My tax dollars at work.

Tom