AnsweredAssumed Answered

Adding a number to field using Calculated value

Question asked by VonnieBrazeal on Nov 24, 2014
Latest reply on Nov 26, 2014 by VonnieBrazeal


Adding a number to field using Calculated value


Hello ! New here and trying to make a simple database ( is any database simple? ) .Here is my problem. I have the following fields...  pkCustomer , LastName, FirstName  .  I want it to generate a unique ID in the pkCustomer field when I enter data in the other two fields. I need it to get the first 3 letters from the Last Name, the first letter of the First Name and then add 0001. If that ID that already exists, then I need it to add 0002. So far, I have it generating the letters by using this in the calculated value option -  Left(LastName;3) & Left(FirstName;1) . that part works great . I just don't know the best way to get it to check for existing ID then add the 001 or 002 etc...

An example of what I need it to do...

pkCustomer  - CusV0001             If this record already exists then it should be CusV002  

Last Name - Custard

First Name - Vanilla


I hope all that makes sense. Thanks !