I apologise if this is a complete 'noob' question.

I am writing a database to record scientific sample data. The established convention has been to name the samples with a 2 letter designation describing what the type of sample is and then a 2 digit serial number based on the sample type

e.g. OF01 OM01 OF02 SC01 OM02 OF03

I want to write a script that will generate that id number.

The only problem is that I have absolutely no idea how to do it!

I am presuming that I will have to find all the records that start OF. Then strip the last 2 characters, find the highest number and then add one.

All help will be greatly appreciated.

Many Thanks

First, to get the number portion of the text, use the GetAsNumber function.

Next, to get the highest number, use the Max function.

But the Max of what?

Set up a relationship of the table to itself based on the sample type. So your final calc would be:

Max( GetAsNumber( SelfBySampleType::ID ) ) + 1

You should be able to use that for auto entry, so you wouldn't necessarily need a script. If you do want to use a script, there are other ways you could accomplish the same thing. E.g., you could pop up a new window, find matching records, sort by id, then go to the last record, add one to that, then close the window.

Thanks very much for your help! I had been going round in circles for over an hour before giving up!

Thanks again.