How to create a custom autonumber

[Toeying]

Hello All,
I have a table where I want to genrate the contactID automatically. The format is HS+mmyy+serialnumber I.e HS is the prefix for all the contactIDs followed the month and year followed by the serial number. so if a contact is introduced today as the 2nd contact for the month, the ContactID would be HS0810002.

2. I also have an age field which I want to be calculated from the date of birth.
3. I have another field in which I want to store the date exactly one year from the date of birth field
4. Also, I want to add a date field which is automatically filled in with today's date.

I hope all these are quite clear and related...
Many thanks for your help.

 

[vbaInet]

Look into using these functions:

Format() for the month and year

DMax("Right([FieldName], 3)", "TableName") + 1 for the increment.

When you're done, put in the Default Value property of the control.

 

[SteveSchapel]

Toeying,

Regarding the date field with the current date, just set the Default Value property of the field to:
Date()

Now, regarding your ContactID, there are a number of ideas we could explore here. But the obvious one is the "HS". If all of the ContactIDs start with HS, then there is no need at all to store this in the ContactID field in your table. Just adds further complication with no benefit. Don't confuse the value of the data with the display of the data. Whenever you want to display the ContactID, on a form or report, it is easy to format it to show with the leading "HS" if that is the requirement of the business procedure -- that's how I would do it.

 

[Toeying]

hello DMax function is not recognised . was that a typing error? thanks

 

[Galaxiom]

To record an age field in a table is pointless and a breach of normalization.
Calculate the age as required in forms and queries from the DOB field and the current date.

Likewise storing a date exactly one year from their birthday.

 

[Toeying]

Hmm Thats an interestin leasson. many thanks. I guess this shows what a newbie I am thanks a lot

 

[Galaxiom]

Search the forum for normalization. There are many threads. It is the most basic concept that new developers have to get tehir head around.

Basically it means that the database cannot store conflicting information. So if something can be calculated it should be rather than storing the result of a calculation which might disageee if the source value was later edited.