Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula based on date & autonumber? (2007)

    I have a field where I would like to have an member number and I already have a MemberID primary key field in the table that is an autonumber. What i would like to do is have the member number field automatically construct a member number based on the year the record was created and the autonumber used as the primary key. For example, if I enter a member today (12/10/2008) and the MemberID (autonumber) is 118, the member number will be 8118, next year (2009) new members entered will be 9XXXX then, 2010, new members entered will be 10XXXX.

    Is this possible within a field? If so, how would I do it?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Formula based on date & autonumber? (2007)

    Assuming you have a field that records the date the record is created.. DateJoined

    Year([DateJoined]) will be 2008, 2009 etc

    Year([DateJoined]) - 2000 will be 8,9, 10 etc. These are numbers, so any leading zeros will be dropped.

    Now you need to add that to the autonumber field MemberID. But we can add as numbers, because that would just make the number a little bigger. So we need to convert the two numbers to strings, and concatenate them.

    me.MemberNumber = Str(Year(me.[DateJoined]) - 2000) & str(me.[memberID])

    This code needs to go in the After Update of the first field on the form you are certain will be filled in (if there is one.)
    You only want it to run for new records, so wrap it with

    if me.newrecord then
    ....
    end if
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula based on date & autonumber? (2007)

    Cool! thanks a bunch.

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula based on date & autonumber? (2007)

    This works well however the result contains a space between the year and the ID number. Whatr should be 826 is 8 26. Any idea how I can change that? Is there a 'trim' function I might use?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Formula based on date & autonumber? (2007)

    I had not tested this, but I see that it does happen. Don't know why, but it seems to be a leading space with the memberID so put a trim function around that.

    Try me.MemberNumber = Str(Year(me.[DateJoined]) - 2000) & Trim(str(me.[memberID]))

    John
    Regards
    John



  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula based on date & autonumber? (2007)

    If you look carefully, there is also a space in the beginning of the string, so you should use :

    me.MemberNumber = Trim(Str(Year(me.[DateJoined]) - 2000)) & Trim(str(me.[memberID]))

    That space come from the + before the number that you don't see. If you would transform a negative number, you should see a - before the numbers
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula based on date & autonumber? (2007)

    Thanks guys, this is perfect.

Posting Permissions

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