Is there away to do this?

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

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
 
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?
Click to expand...


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.
 
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.
Click to expand...
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?
Click to expand...

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.
Click to expand...
 
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
 
Back
Top