Is there away to do this?

[ryan.fitzpatrick3]

In a query I have one field that would have a size of an object, i.e.
10 OZ, for weight of finished good product.

Now is there away to only take the numerical value of this and omit
the letters? I know left([fielda],2) works but some items are 1.25 oz,
10.145 lb, .134 fz, etc, all different whereas the left 2 wouldn't
work. Any help?

Ryan

 

[Dirk Goldgar]

In a query I have one field that would have a size of an object, i.e.
10 OZ, for weight of finished good product.

Now is there away to only take the numerical value of this and omit
the letters? I know left([fielda],2) works but some items are 1.25 oz,
10.145 lb, .134 fz, etc, all different whereas the left 2 wouldn't
work. Any help?

The Val() function will return the leading numeric value from a string. It
stops at the first character it can't interpret as a number. Check it out
in the VB help file. Note that some character combinations may be
interpreted as numeric that you wouldn't expect; for example, Val("4E5") =
400000, because Val interprets that as exponential notation.

 

[ryan.fitzpatrick3]

Good deal, now can I put this in the a new query field like
val([fielda])?

In a query I have one field that would have a size of an object, i.e.
10 OZ, for weight of finished good product.

Now is there away to only take the numerical value of this and omit
the letters? I know left([fielda],2) works but some items are 1.25 oz,
10.145 lb, .134 fz, etc, all different whereas the left 2 wouldn't
work. Any help?

The Val() function will return the leading numeric value from a string. It
stops at the first character it can't interpret as a number. Check it out
in the VB help file. Note that some character combinations may be
interpreted as numeric that you wouldn't expect; for example, Val("4E5") =
400000, because Val interprets that as exponential notation.

 

[Dirk Goldgar]

Good deal, now can I put this in the a new query field like
val([fielda])?

Sure.

 

[ryan.fitzpatrick3]

Yup, got it to work, thanks a million.

Good deal, now can I put this in the a new query field like
val([fielda])?

Sure.

 

[Jeff Boyce]

If your "size" sometimes is in ounces, sometimes in pounds, sometimes in
(?)"fz"s, consider using TWO fields, one for the numeric value, the other
for the "unit of measure".

Regards

Jeff Boyce
Microsoft Office/Access MVP