Help With Formatting A Field

I have a table with several records. I want to be able to give each record in the table a distinct identification number, I.E. record Number.

I want the record number to be formatted in a manner that when it is looked at it has meaning other than a series of numbers. I would like to have the record number represents the following: The first 4 numbers represent the building number; the building number is a field in the table. The next four numbers will represent a date (also a field on the table) day and month only. The last number (s) should be in sequence 1,2,3…base on the number of records.

For Example building 21 was inspected on August 23 we found 3 problems, so our primary key numbers would read 00210823001, 00210823002, 00210823002. And if we went to building 1255 on the same day a found 2 problems the primary key numbers would be 12550823001, 12550823002.

So, 1255 0823 001
1255 –building number from table field
0823 – from date field on the table (but only month and day)
001, 002…normal sequence of numbers.


Can anyone please help with this? I sure its probably simple but my Access experience is limited.