Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    Unanswered: unique primary key

    i have a database with a table called patient with fields called chartnumber, lname,fname. after the user enters the first name and the last name i would like the chart number to be filled in with this format, first 3 letters of lname,first 3 letters of fname, then at the end to add 0001, but if the number 0001 exists then to make it 0002. for example user enters john doe the chart number should equal doejoh0001 if that exists since my chartnumber field is my primary key then it would change it to doejoh0002. i have tried this with the experessions like left([lname],3) & left([fname],3) but i cant figure out how to add the 0001 and also change it to 0002 and 0003 and so on . someone has ansered this question with an sql answer but i need it in access and in simple terms with directions includeing how to run the code from a command button on my open form etc. please help. also if there is no record how to deal with a null value.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    How about having an ID column that you retrieve the next ID # from? Basically a table of IDs ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •