Unsure how to define fields from others

[Sambo]

In my database I'm trying to define fields from another field's value within the same database.

e.g. ITEM : Name of product SF1212

I would like to be able to create a field which can identify if the ITEM's value contains a serial number or not and to work out from the serial number a date. The serial numbers are after the description of the product and are in two formats.

e.g. SF1

[BobWeaver]

LeftWords(Middle(ITEM,Position (ITEM," SF",1,1)+3,50),1) <--note the quoted text has a leading space.

will give you the serial number without the "SF" prefix.

Then you can check this resulting serial number with

Case(Left(SerialNumber,2)="95", 1995,1990)

to get the year

[Sambo]

Thank you Bob, now just having problems with setting the year.

1. In the serial number for the year 2000 the serial number has prefix 00. I've tried using Case(Left(Collection number, 2)="00",2000) but it doesn't work.

2. Other years has to come from a number range eg 1- 250 for 1990 and 270-400 for 1994.~The Serial numbers can't be changed as they have already been published. I've tried using Case(SerialNumber >=270,1994,SerialNumber >=1,1990)

but this also isn't satisfatory.

[BobWeaver]

So you mean that from 1990 to 1994 the serial numbers were 3 digits, and then starting in 1995, the serial numbers were prefixed with the last two digits of the year creating a 5 digit serial number?

You probably have the serial number set up as a number field. In that case, the leading zeroes for years 2000 and later will be stripped off. So 00123 (for 2000) will become 123, and 02123 (for 2002) will become 2123. You need to set the calculation result type to text.