How do I split field

[Lucien]

I have a field that contains a Qty and unit:

311 ea
12 ea
5 ea
....etc.

How can I return only the number and exclude the "ea". This field is
currently a text field and I have more than 3 milion records.
Can I split this field into 2 or at least just return the number in a query?

 

[Klatuu]

In most inventory database applications, the unit and quantity are two
different fields. The best approach would be your first, which would be to
split it into two fields.

I think your problem may be that since you have 3 million records, you might
be bumping up against the access size limit of 2GB. This means it would be
best if you can do it with a couple of update queries. Other than that, the
process is pretty straight forward.

First you would need to modify your table design to create the new field. I
would make the new field numeric to hold the quantity because the existing
field is text.

Then in the update query, you could update the new Qty field to:

Val([OldField)

To use your example: 311 ea would return 311
The old field will still contain 311 ea
The new field will now be a numeric 311

Then run a second query that would take the quantity off and leave only the
unit. This formula assumes there will always be a space between the number
and the unit.
Here is a formula for the update value:
right(trim(x),len(trim(x))-instr(trim(x)," "))
the original field will now contain
ea

 

[Lucien]

Great information!

Thanks for the help, it worked perfectly.

In most inventory database applications, the unit and quantity are two
different fields. The best approach would be your first, which would be to
split it into two fields.

I think your problem may be that since you have 3 million records, you might
be bumping up against the access size limit of 2GB. This means it would be
best if you can do it with a couple of update queries. Other than that, the
process is pretty straight forward.

First you would need to modify your table design to create the new field. I
would make the new field numeric to hold the quantity because the existing
field is text.

Then in the update query, you could update the new Qty field to:

Val([OldField)

To use your example: 311 ea would return 311
The old field will still contain 311 ea
The new field will now be a numeric 311

Then run a second query that would take the quantity off and leave only the
unit. This formula assumes there will always be a space between the number
and the unit.
Here is a formula for the update value:
right(trim(x),len(trim(x))-instr(trim(x)," "))
the original field will now contain
ea

I have a field that contains a Qty and unit:

311 ea
12 ea
5 ea
...etc.

How can I return only the number and exclude the "ea". This field is
currently a text field and I have more than 3 milion records.
Can I split this field into 2 or at least just return the number in a query?