Need query help for pulling out two pieces of data from a field MS Access

MyDanes
MyDanes used Ask the Experts™
on
DESCRIPTION      
"""8.250 X 10.430 X 1/8""""""      
"""11.50 X 11.50 X 1/8"""" Silkscreen""
15.594 x 15.594 x 3/16 CLR
5.875 X 8.8125 X 3/16 Edged and Drilled

ok the above is a sample of what I might encounter in the field in question

1.  I need to extract the first number BEFORE the x so
8.250
11.50
15.594
5.875

2.  I need to extract the number AFTER the x

10.430
11.50
15.594
8.8125

Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
1.  Replace(Split([yourField], "x")(0), chr$(34), "")

2.  Split([yourField], "x")(1)

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:

To clarify:
Are your questions really:
"I need to extract the first number BEFORE the *First* x."
"I need to extract the number AFTER the *First* x."
(as there are two x's on each line followed by numbers)


..and what is the *exact* final output you need?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
BTW, you cannot use the Split function this way in a query, so if you need to do this in a query, you would need to wrap it in a function.  This particular function will return a NULL value if the string does not contain the SplitChar, it will also return a NULL if the Element you ask for is <1 or > the number of segments that would be created by the Split function.

Public Function fnSplit(SomeValue as Variant, SplitChar as string, Element as integer) as Variant

    if isnull(SomeValue) Then
        fnSplit = NULL
    elseif instr(SomeValue, SplitChar) = 0 then
        fnSplit = NULL
    elseif Element < 1 then
        fnSplit = NULL
    elseif Element > Len(SomeValue) - Len(Replace(SomeValue, SplitChar, "")) + 1 then
        fnSplit = NULL
    else
        fnSplit = Replace(split(SomeValue, SplitChar)(Element-1), chr$(34), "")
    endif

End function
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
To use this function in a query, you might do:

SELECT Description, fnSplit([Description], "x", 1) as [First size], fnSplit([Description], "x", 2) as [Second size]
FROM yourTable

Author

Commented:
I am sorry you are CORRECT number BEFORE 1st x and number after first x before next x

first number will land in one field and the second will land in another field

Length x width
length = 1st num
width = 2nd num

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial