Need and opinion - Member ID

[dniezby]

What do you think would be the best way to generate a membership ID number? Here is the idea I have. When a new member is added to my member table I have a timestamp. I also have the actual table primary key 'ID'. What I wanted to do was simply join the year part of the timestamp and the record's ID. I'm just having problems creating the query.

member id:[timestamp]& [id]

Now this works but I don't know how to do two things. How do I extract JUST the year from my timestamp field and how do I add leading zeros to the ID when it generates the member id? So instead of generating an ID that looks like 04042013 17:437 it would look like 20130007. If figure this way, the member id is generated on the fly for reports and print outs and such. Though later, I will need to do searches based on the member's ids and lookups by member ids. Should I use a different method or hardcode their ID into the member's table?

[pbaldy]

Try

Year([timestamp]) & Format([id], "0000")

[dniezby]

That worked. Thanks.

[pbaldy]

No problem. Welcome to the site by the way!

[dniezby]

Thanks. I'll be here a lot. At least until I finish my home project.

[dniezby]

I tried to use this formula in the same query to add an Age field.

AGE:Year([dateofbirth])-Year([timestamp]) and the result is correct it's just showing up as a negative number. Also, is it possible to compensate for whether or not the persons birthday has passed or not?