# Thread: Formula based on date & autonumber? (2007)

1. ## 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. ## 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

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

Cool! thanks a bunch.

4. ## 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. ## 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

6. ## 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

7. ## 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
•