4 Replies Latest reply on Nov 26, 2014 10:07 PM by VonnieBrazeal

    Adding a number to field using Calculated value



      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 !