Truncating number but not by decimals...

[LERC Assistant]

I want to create a new field based on a field containing large numbers [ie. '81391002;]. I want the new field to shorten this number to the first two or three numbers.

For example:

'81391002' would be '81'.

Currently, the field is in a number format.

Can anybody help?

Thanks,

Sara

Edited December 16, 2005 by Guest

[RalphL]

The Left function could be used to get the first 2 or 3 numbers.

You could also use the Div function if the number always has the same number of characters.

[LaRetta]

Hi Sara,

Can you provide an example on why you want this? What kind of numbers are they? If you have:

81391002

3125

... and want them both truncated to the first two digits, the value will be incorrect. It will indicate that the second number is just short of half the value of the first. You need to apply a function which treats both numbers equally regardless of their size. Dividing both numbers by a standard, such as 100 would produce:

813910

31

Is that closer to what you want? But the numbers may never be the exact same length. If in fact these numbers aren't really NUMBERS but rather number-text, then taking the left two digits would work using Left(numField ; 2) as Ralph indicates. But your post of 'truncating numbers but not by decimal' implies real numbers. We need to know the purpose of this 'number' field.

[SurferNate]

LaRetta,

I might guess that it's for an index. If you have thousands of serial numbers, and the first (left) two digits actually mean something. Then one may wish to filter based upon those digits.

LERC, if you tell us more about your data and intent we may be able to help better.

Does every field/record contain the same number of characters?

Does this field absolutely have to calculate dynamically as a number? Or is it really just a stored value?

Does this data relate to more than just a numeric value? (such as a structured identification number which actually tells us something about the item)

[LERC Assistant]

Wow, I appreciated such a great response.

Basically, the field is a type of code [NAICS] that identifies the company. However, the users of the interface/database want a 3-digit code [think of this like a hierarchy] since these NAICS descriptions need to be condensed.

1] Yes, every field has the same number of characters.

2] No, the field does not necessarily have to be calculated as a number- it just corresponds to the appropriate description.

example: CODE: 6231100 Description: NURSING CARE FACILITIES

Should be: CODE: 623: Description: Health Care and Social Assistance

3] So yes, this data does relate to more than just a numeric value.

I hope this helps you help me

=) I tried the 'left' function as a test, and this did work. However, I'm not sure what's the most efficient way to group the new descriptions.

What do you think?

[SurferNate]

That's better,

I might suggest that you have a stored, calculated field (maybe name it NAICSCategory) that gets Left(NAICSCode;3). Now you have an indexed value in each record that contains the Category code. If the subsequent numbers also tell you something, pull them also into their own field.

Now, you can create a Table of NAICSCODES that contains the number and description of all of your codes. Relate the company to the code via the NAICSCategory field and you can pull the category description from your table of codes and descriptions. Make this a multipurpose table so you can add records for subcategories as required. (Just add a Subcategory field and a SubCategory Description to your NAICSCODES table, these records will not contain any Category info but only Subcategory info) This way you can add, change, and omit descriptions on the fly, and each related company record will automatically have the updated description available instantly.

P.S. - I've beeen told, on several occcasions, that TEXT is almost always better than NUMBER for a field definition. This is a great case. If you have a Code that begins with "012" a NUMBER field will strip the zero and make life more difficult for you. However, you can perform mathematical functions on TEXT fields that contain numbers of you wish.

[LERC Assistant]

Many thanks for the great advice!