How to select a partial filed

[Sean]

I have some sales data and in the quantity sold filed the qty of the item
sold is combined with its unit of measure. For eample; 5 ea, 10,000 PAC, 125
EA, 2,345 PCS. This is a text field and after each number is a space before
the unit of measure. How do I get a query to just give me the values before
the space so I can get rid of the Unit of measure. I cannot do a mid becuase
the quantities are always different.

Thanks,
Sean

 

[Maurice]

Sean,

Instead of Mid try using InStr([Fieldname]," "). With the instr you can
search for the delimiter you specify in this case being the space. If you
combine this with the left string you might just get your answer fixed

Maurice

 

[Amy Blankenship]

I have some sales data and in the quantity sold filed the qty of the item
sold is combined with its unit of measure. For eample; 5 ea, 10,000 PAC,
125
EA, 2,345 PCS. This is a text field and after each number is a space
before
the unit of measure. How do I get a query to just give me the values
before
the space so I can get rid of the Unit of measure. I cannot do a mid
becuase
the quantities are always different.

The good answer is that these should be two separate fields, probably with
the units actually pointing to a lookup table. The easy answer is to use
the Left function.

HTH;

Amy

 

[Jerry Whittle]

Hi Sean,

Please show us how the data looks in each record and what you want
extracted. For example.

5 ea, 10,000 PAC
125 EA, 2,345 PCS

Assuming I got the above correct, do you want 5 or 5 ea or is it the 10,000
that you need.

If just the "5", use a Val function. Val("5 ea, 10,000 PAC") returns 5.

If the "5 ea", is there always a comma then a space after the "ea"?

 

[John Spencer]

Use the following to return just the string prior to the first space

LEFT([Quantity], Instr(1,[Quantity] & " "," ")-1)

If you want to turn the string into a number you can use a conversion
function. CDbl is going to be the most generic, but it will have problems
if you try to handle a non-number string.

CDbl(LEFT([Quantity], Instr(1,[Quantity] & " "," ")-1))

IF that errors you can test the Left expression first to make sure it
returns a number
IIF (IsNumeric(TheExpression),
CDbl(TheExpression),WhatValueYouWantReturnedForNonNumbers)

IIF(IsNumeric(LEFT([Quantity], Instr(1,[Quantity] & " ","
")-1),CDbl(LEFT([Quantity], Instr(1,[Quantity] & " "," ")-1)) ,Null)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sean]

I only want the number. Ex. "5 EA" would be 5, "10,000 PAC" would be
10,000, "250 PCS" would be 250.

 

[Jerry Whittle]

In that case you'll need a combination of the Val and Replace functions. The
Val function looks at the string and grabs the numbers starting at the front
until it hits a character that it doesn't think is a number. Suprisingly,
periods don't bother it; however commas do. Therefore replace all the commas
with an empty string. For example:

Val(Replace("10,000 PAC",",","")) returns 10000.

In a query just replace "10,000 PAC" with the field name.

 

[Amy Blankenship]

In that case you'll need a combination of the Val and Replace functions.
The
Val function looks at the string and grabs the numbers starting at the
front
until it hits a character that it doesn't think is a number. Suprisingly,
periods don't bother it; however commas do. Therefore replace all the
commas
with an empty string. For example:

Val(Replace("10,000 PAC",",","")) returns 10000.

In a query just replace "10,000 PAC" with the field name.

Yet another reason to have the quantity in its own (numeric) field...