From text to number (avarage)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
..
 
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
 
Back
Top