Unique Number code

[agorman]

Hi,

I am trying to generate a new field with a unique number. I have used the number from the ID field (this is an autonumer and key) and the initials of the first and last name field and I would like to add the year at the end.

I can get it to work with the whole date but have got stuck now. Can anyone help me with the last bit please - this is what I have but instead of the date at the end I would like the year so my unique number might look like 1AG2009
2PM2009
etc
This is the code I am using in the query

Unique Number: [ID]&""&Left([FirstName],1)&""&Left([LastName],1)&""&Date()

Thanks

Adrian

 

[boblarson]

Unique Number: [ID] & Left([FirstName],1) & Left([LastName],1) & Year(Date())

 

[agorman]

Thankyou for that.

Regards

Adrian

 

[gemma-the-husky]

if you have an autonumber, why do yuo need another unique key?

 

[agorman]

I have the autonumber as the key which goes 1,2,3 but I want to give a student a unique personal number with the intial of their first and last name and the year they where registered.

The easiest way I could think of (being somewhat of a novice) was to create a field in a query which incorporated this in, and the autonumber was added to the front.

If I am the first person in the database my unique ID would be 1AG2009. If there was another person with the same initials (Alan Gill for example) and he was my second entry into the DB, his number would be 2AG2009.

The unique field is on the form but not the autonumber field , so as the persons first and last name are entered it automatically generates the ID number

I guess there is a better more efficient way to do reach the same result, but I thought this would work okay and is simple.

 

[boblarson]

I guess there is a better more efficient way to do reach the same result, but I thought this would work okay and is simple.

Sounds reasonable to me as long as you aren't trying to create a primary key.

 

[agorman]

I have set the field called 'ID' to be an Autonumber and set it as the primary key. I fugured that the result in the 'unique number' field generated (with the primary key as the first digit) will always be unique as the Primary Key is autonumber(ing).

My goal is to be able to assign a Unique number to a student with the initials of the first and last name, and the year in which they where added to the db.

Is there a more effective way of doing this?

I did not seem to be able to get an autonumber field to include these details and set it as Primary key.