select 3 digit numbers and exclude 6 digit numbers

[jobillborf]

Hi, there is a field in our database called a tag number.

The field is populated with a 3 digit number and a six digit number. I would like to list all the 3 digit numbers and exclude all the 6 digit numbers.

Thank you for your help.

 

[synapsevampire]

If it's a text field use:

left({myfield},X) where X is the amount of characters you want.

mid({myfield,X,Y) where X is the starting point, and Y is the number of characters you want.

If it's a numeric field.

totext({myfield},0) will return the field as text, so you can then apply the above. Note the ,0 which excludes any decimal precision (totext may change 12345678 to "12345678.00&quot, which if your number has none of, leave intact, if you do have a fixed decimal precision, adjust accordingly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

You say that the field is populated with 3 digit and 6 digit numbers....right...it is not a string field....If so please define it as such.

the easiest way to separate the 3 digit numbers from 6 digit numbers is use the following in a record select formula

{Table.field} <= 999

that ought to do it.

If it is a numeric string...then

tonumber({Table.field}) <= 999
Jim Broadbent

 

[synapsevampire]

If:

&quot;The field is populated with a 3 digit number and a six digit number.&quot;

should read:

&quot;The field is populated with a 3 digit number *OR* a six digit number.&quot;

then ngolem pegged it.

Otherwise, my post should handle it, though I should have mentioned that you can do the following:

text fields:

right({myfield},X) returns X characters from the right side

and

length(trim({myfield})) might be used to test for the length being 9, or any parsed out portion, as you my want to pad the 3 digit number if it starts with 0's.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV - true...always miss those AND/OR's...seemed to simple to me...I would have expected a separator of somekind though if both were present Jim Broadbent