Great information!
Thanks for the help, it worked perfectly.
"Klatuu" wrote:
[Quoted Text] > 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" wrote:
>
> > 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?
> >
> >
> >
|