Hi,

Is it possible to store the record numbers in a prescribed format in a field. For e.g.

For record no. 1, the field should store C001
For record no. 12, C012
For record no. 123, C123


Could anyone help me on this?

Thanx

Tyna

Why do you want to do this? Access has an autonumber field that will assign numbers in ascending order starting at 1. However, if you delete records, they will leave gaps. You can always join a "C", a maximum of 2 leading zeros and the record number in a query for viewing or feeding into a form or query.

Also, could your record numbering scheme ever pass C999?

Hi,

Thanx for the reply.

I'm aware of the autonumber feature but just wondered whether there is any other way out whereby the user does not have to enter the data in the prescribed format.

Could you pls elaborate on the solution that you gave me. I do not know how to automatically store record numbers in the field. New to Access.

Further, the data would not surpass C999.

Kindly help.

Regards...

I'm aware of the autonumber feature but just wondered whether there is any other way out whereby the user does not have to enter the data in the prescribed format. If you're looking to have the user enter "record numbers" in a prescribed format, then allow someone to "violate" that rule, then the autonumber feature is not what you want.

What will this number be used for? An autonumber is best used to enumerate data that needs to be unique identified but that does not need to be referred to by that autonumber. For example, sales transactions. You need to keep them all separate and do separate calculations on each one, but you don't need to know that a particular sales transaction was number 215. It's just a quick way of numbering the records.

If you need to assign each record an identifier that will somehow point to the record itself (like what a social security number does for a person), then you need to create your own record numbers. Access will not automatically do it for you.

Before I explain any more, please explain how you plan to use that number.

A log table is to be maintained and the field say EstiId would store the numbers starting from 1 prefixed with an alphabet. Alphabet is the first letter of another field frm the same table.

This table would be zapped at the end of the day, hence, autonumber feature can't be considered as next day the same table is to be used and the number has to start from 1 again.

During data entry the user would be shown the rest of the fields from the log table except EstiId which shd get updated the moment the record is added.

So I thought of concatenating alphabet+record number but don't know how to do it.

I hope you got an idea about my problem.

In your previous reply you had mentioned that one can join alphabet and the record number in a query. Kindly explain how?

Thanx...

So I thought of concatenating alphabet+record number
That sounds exactly like how you should handle this.

Look in the Access help system on how to calculate values in queries. One of the basic functions of queries is to allow you to manipulate data. If I hate a letter in a field and a number in another field, I can join them together. If the letter field is called [Letter] and the number field is called [Number], I can create a new field called [Result] that is fed by the formula [Letter]&[Number] so that "A"&"1" becomes "A1" in the [Result] column.

By the way, using a query to calculate those record identifiers does NOT store them permanently. One big rule of database design is to never store data that can be calculated from other data in the same database.