Formula based on date & autonumber? (2007)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

Cool! thanks a bunch.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks guys, this is perfect.

Reply | Quote