Jump to content

Unsure how to define fields from others


This topic is 7876 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 frown.gif as they have already been published. I've tried using Case(SerialNumber >=270,1994,SerialNumber >=1,1990)

but this also isn't satisfatory.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7876 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.