How to partially delete text from a single field in 200k records.

[pcdummy]

Hi Guys.. I am pulling my hair out with this one. I am converting some huge table from an old DBASE format.. No problem there. However, I have one table tblProdAux that has two fields [Part_Num] and [Weight] part number is fine - simple number field.. however i need to convert the [weight] field to a number field as well. Currently is text field with contents such as "1.25 lbs." Does anyone know an easier way than going an manually editing the 200,000 records.. I think an update query would do it.. But I have no clue on how to begin.. PLEASE Help.. Thanks!

 

[dcx693]

You can use the Val() function like this Val("1.25 lbs.") in a query to get the numeric piece. To get the units, use the Instr() function to find the space, then use the Mid function like this: Mid("1.25 lbs.",Instr(1,"1.25 lbs."," ")+1).

 

[pcdummy]

You can use the Val() function like this Val("1.25 lbs.") in a query to get the numeric piece. To get the units, use the Instr() function to find the space, then use the Mid function like this: Mid("1.25 lbs.",Instr(1,"1.25 lbs."," ")+1).

Thanks for the quick reply.. How do I actually use these values to separate them then?

 

[pcdummy]

Thanks I got it!! Just used VAL function in a separate Text Box works like a charm thanks alot!