From text to number (avarage)

U

Ulto

Hi
I got a table over some companies where one of the colums is the number of
employees in the company, atm that field is a text field, co employee number
can be listed as "5-9" or "10-19" or an exact number.

I would like to changes the the colume so in the fields you will se the
average emp number instead of a range so i easier can sort on that field.

Any1?
 
J

John Spencer

If you can guarantee the the numbers are always either a number or always
two numbers separated by a dash then you could try the following untested
expression

IIF(IsNumeric([TheField]), Val([TheField]), IIF([TheField] Like "*-*",
Val([TheField]) + Val(Mid([TheField,Instr(1,[TheField,"-"])+1)) /2,Null))

If you don't like fractional values then instead of dividing by 2 (/2) use
integer division (\2) to get the lower number. After all what is the
halfway point between 10 and 19? Is it 14, 15, or 14.5? The halfway point
for 5-9 is easier, it is 7.

Good luck with the above expression
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

KARL DEWEY

Why not change to High and Low number fields so that your current data would
look like this --
Now Low High
5-9 5 9
10-19 10 19
6 6 6
 
Top