Creating a unique value from several others..

[chemram]

I am writing a database for a local government authority that refers to clients with a unique ID number, made up of the first 3 letters if the surname, then the first 3 letters of the Given name , then th Date of birth.... eg: John Smith, born 12/3/1965 would become "smijoh1231965". How would i create a macro or expression to perform this calculation?

 

[Atomic Shrimp]

You could use an update query to poulate a blank field in the table, like this:

UPDATE clients SET clients.UniqueID = Left([Surname],3) & Left([firstname],3) & Day([DOB]) & Month([DOB]) & Year([dob]);

But it's generally considered to be bad practice to store a value that can be calculated from static data, so you could just use the expression:

(Left([Surname],3) & Left([firstname],3) & Day([DOB]) & Month([DOB]) & Year([dob]))

To create the uniqueID 'on the fly' in your queries etc.

One more thing though; what if there are TWO John Smiths born on the same day?

HTH

Mike

[This message has been edited by Mike Gurman (edited 12-28-2000).]