none
Calculating a field based on 3 other fields(one date and 2 text)

    Question

  • I have three fields:  [LName],[FName] and [DOB]

    I need to generate a new field that will create a 9 character ID in this format ##ABC####

    the first two numbers would be the two digit day of [DOB]

    the first letter is the the first character of [Lname]

    the second letter is the THIRD character of [LName]

    the third letter is the first character of [FName]

    the next two numbers are the two digit month of [DOB]

    the final two numbers are the two digit year of [DOB]

    For example: Smith, Joe 04/01/1970 would be: 01SIJ0470

    Any suggestions?

    Wednesday, July 10, 2013 8:38 PM

Answers

  • I have three fields:  [LName],[FName] and [DOB]

    I need to generate a new field that will create a 9 character ID in this format ##ABC####

    the first two numbers would be the two digit day of [DOB]

    the first letter is the the first character of [Lname]

    the second letter is the THIRD character of [LName]

    the third letter is the first character of [FName]

    the next two numbers are the two digit month of [DOB]

    the final two numbers are the two digit year of [DOB]

    For example: Smith, Joe 04/01/1970 would be: 01SIJ0470

    Any suggestions?

    Hi spankovich,

    It is a matter of some string manipulation. You can try:

      code = Format(Day(dob), "00") _
           & Left(lname, 1) _
           & Mid(lname, 3, 1) _
           & Left(fname, 1) _
           & Format(Month(dob), "00") _
           & Right(Year(dob), 2)

    Imb.

     

    • Proposed as answer by KCDW Wednesday, July 10, 2013 9:37 PM
    • Marked as answer by spankovich Wednesday, July 10, 2013 10:29 PM
    Wednesday, July 10, 2013 8:59 PM
  • No, I do want to store this in the table.  If the name were to change, then the ID would as well. 

    Selina

    Hi Selina,

    You can place the calculation in a sub,

    Sub Update_code() 

    Me.Code = Format(Day(Me.DOB), "00") _ & Left(Me.LName, 1) _ & Mid(Me.LName, 3, 1) _ & Left(Me.FName, 1) _ & Format(Month(Me.DOB), "00") _ & Right(Year(Me.DOB), 2)

    End Sub

    I assume the control of the calculated field is "Code". In the AfterUpdate event of LName, FName and DOB you then execute the sub Update_code.

    Imb.

     

    • Marked as answer by spankovich Wednesday, July 10, 2013 10:28 PM
    Wednesday, July 10, 2013 10:03 PM
  • I know that this is typically not recommended in database design.  However, this ID that I need to generate is an ID that our state uses to identify offenders - My users have to create this number at some point and they use it to report information to the state.  Right now I am relying on the users to correctly create the ID.  This is close to impossible.  I DO need this number stored in the table.  Please help.

    Selina

    Hi Selina,

    See my previous answer.

     

    Imb.

     

    • Marked as answer by spankovich Wednesday, July 10, 2013 10:28 PM
    Wednesday, July 10, 2013 10:17 PM

All replies

  • I have three fields:  [LName],[FName] and [DOB]

    I need to generate a new field that will create a 9 character ID in this format ##ABC####

    the first two numbers would be the two digit day of [DOB]

    the first letter is the the first character of [Lname]

    the second letter is the THIRD character of [LName]

    the third letter is the first character of [FName]

    the next two numbers are the two digit month of [DOB]

    the final two numbers are the two digit year of [DOB]

    For example: Smith, Joe 04/01/1970 would be: 01SIJ0470

    Any suggestions?

    Hi spankovich,

    It is a matter of some string manipulation. You can try:

      code = Format(Day(dob), "00") _
           & Left(lname, 1) _
           & Mid(lname, 3, 1) _
           & Left(fname, 1) _
           & Format(Month(dob), "00") _
           & Right(Year(dob), 2)

    Imb.

     

    • Proposed as answer by KCDW Wednesday, July 10, 2013 9:37 PM
    • Marked as answer by spankovich Wednesday, July 10, 2013 10:29 PM
    Wednesday, July 10, 2013 8:59 PM
  • Genius!  I got that to work in a query.  I couldn't get it to work in a calculated field in my table.  If I had that query on a form, how could I get that to update to a field in my table? 
    Wednesday, July 10, 2013 9:14 PM
  • Genius!  I got that to work in a query.  I couldn't get it to work in a calculated field in my table.  If I had that query on a form, how could I get that to update to a field in my table? 

    Hi spankovich,

    In the ControlSource of the calculated field you can place this expression:

         = Format(Day(dob), "00") & Left(lname, 1) & Mid(lname, 3, 1) & Left(fname, 1) & Format(Month(dob), "00") & Right(Year(dob),2)

    Imb.

    • Proposed as answer by KCDW Wednesday, July 10, 2013 9:37 PM
    Wednesday, July 10, 2013 9:26 PM
  • I am so sorry - I am not sure how to get that calculated field to update the real field in the table.  Does that make sense?

    Selina

    Wednesday, July 10, 2013 9:40 PM
  • You don't store the calculated field in the Table. Calculated fields display the calculation based on current information. If information were to change then the value stored in the calculated field would be in error. This would be true if for some reason a persons name changed. You should only display this value on the Form.

    Chris Ward

    Wednesday, July 10, 2013 9:42 PM
  • No, I do want to store this in the table.  If the name were to change, then the ID would as well. 

    Selina

    Wednesday, July 10, 2013 9:49 PM
  • No, I do want to store this in the table.  If the name were to change, then the ID would as well. 

    Selina

    Hi Selina,

    You can place the calculation in a sub,

    Sub Update_code() 

    Me.Code = Format(Day(Me.DOB), "00") _ & Left(Me.LName, 1) _ & Mid(Me.LName, 3, 1) _ & Left(Me.FName, 1) _ & Format(Month(Me.DOB), "00") _ & Right(Year(Me.DOB), 2)

    End Sub

    I assume the control of the calculated field is "Code". In the AfterUpdate event of LName, FName and DOB you then execute the sub Update_code.

    Imb.

     

    • Marked as answer by spankovich Wednesday, July 10, 2013 10:28 PM
    Wednesday, July 10, 2013 10:03 PM
  • I know that this is typically not recommended in database design.  However, this ID that I need to generate is an ID that our state uses to identify offenders - My users have to create this number at some point and they use it to report information to the state.  Right now I am relying on the users to correctly create the ID.  This is close to impossible.  I DO need this number stored in the table.  Please help.

    Selina

    Wednesday, July 10, 2013 10:04 PM
  • I know that this is typically not recommended in database design.  However, this ID that I need to generate is an ID that our state uses to identify offenders - My users have to create this number at some point and they use it to report information to the state.  Right now I am relying on the users to correctly create the ID.  This is close to impossible.  I DO need this number stored in the table.  Please help.

    Selina

    Hi Selina,

    See my previous answer.

     

    Imb.

     

    • Marked as answer by spankovich Wednesday, July 10, 2013 10:28 PM
    Wednesday, July 10, 2013 10:17 PM
  • Imb.

    THANK YOU!  It seems I get hung up on the simplest things!  You have no idea how much easier my users lives have just gotten.  Is this just a hobby for you? 


    Selina

    Wednesday, July 10, 2013 10:28 PM
  • No, I do want to store this in the table.  If the name were to change, then the ID would as well.
    Well, it is already stored in the table as elements of the values in the FName, LName and DoB columns, so storing it again in a single column achieves nothing.  On the contrary, it merely opens the database to the risk of update anomalies.  Remember that the result table of a query is a table, just not a base table, so can be used exactly as you would a base table.

    A caveat about using this combination of values as a key:  I was once present at a clinic when three patients arrived, all female, all with the same first and last names and all with the same date of birth.  So any table of people must always have a column of guaranteed distinct values as its key, usually an autonumber.  Any combination of personal data cannot be guaranteed to be distinct, so is completely unsuitable as a key.


    Ken Sheridan, Stafford, England

    Wednesday, July 10, 2013 10:37 PM
  • Ken - thanks for your input - it actually made me look at this from a different perspective.  As far as the duplicate names - it has actually happened but the state's remedy is to replace the third letter with an x.  I do still have an autonumber to uniquely identify everyone but this ID is needed and I was never sure how to generate it automatically.

    Selina

    Wednesday, July 10, 2013 11:13 PM
  • And what about John Li?

    Groeten, Peter http://access.xps350.com/

    Thursday, July 11, 2013 6:08 AM
  • Imb.

    THANK YOU!  It seems I get hung up on the simplest things!  You have no idea how much easier my users lives have just gotten.  Is this just a hobby for you? 


    Selina

    Hi Selina,

    Yes, it is just a hobby, but a little "exploded".

    Continuing the rest of the discussion, in line with Ken's advice:

    There is a difference between Primary Keys and (Non-primary) Unique keys. For identification you can use this ID, having an Unique Key. For making the relations with other records I would not use this field as a Primary Key.
    Personally I only use Autonumber fields as Primary Keys to maintain the relations to other records. For the user these Autonumber fields are completely meaningless.

    Take care of that.

    Imb.

     

    Thursday, July 11, 2013 7:07 AM
  • 1) This ID that I need to generate is an ID that our state uses to identify offenders

    2) No, I do want to store this in the table.  If the name were to change, then the ID would as well.


    Selina

    1) My point follows the scenario.

    I hope that the offense of Patricia Haralson doesn't go on the Record of Paul Harvey or else there shared birthday will not be a "Good Day." Since they both were Born August 16, 1932 both there codes will be 16HRP0832.

    Point is:

    Since you are looking to automate this, you should take it a step further. When a person causes the code to generate the code it should also look to see if the stored value is in the Table already Before Updating the new Record. If the value does not exist then save Else look for Value looping through and +1 to the end of the String so that Patricia is 16HRP0832 and Paul is 16HRP08321. That way when Peter Harris shows up in the System he will be 16HRP08322.

    The solution your State has of someone manually changing the 3rd number to x will fail when Peter enters the arena.

    2) This depends on how the name is updated. If it is updated through code manipulation then the calculation will not run and the number will not be updated. Then if Patricia gets a divorce from Mr. Haralson so now is Patricia Wright then her code should be 16WI0832 but still is listed as 16HRP0832.

    3) You will also want to make sure that the calculation pops up a message if any of the three fields are Null(Validation).

    4) In addition to marking Imb's Posts as Answer, you should also Vote them as Helpful since they were helpful to you (and any others that may have been helpful). The Vote Icon is just to the left of each post.

    Thank you for your time!

    ps...I see Peter's post brings up a very important point of those who don't have a third letter in the last name. For instance Bruce Li, Bruce Le & Bruce Lu.

    pss...All the names in this post are real names and birthdays I looked up on the internet. I wanted to make sure the scenario could indeed exist and it does.

    Hth


    Chris Ward

    Thursday, July 11, 2013 1:52 PM