Avatar of MyDanes
MyDanes
 asked on

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

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!
Microsoft Access

Avatar of undefined
Last Comment
MyDanes

8/22/2022 - Mon
Dale Fye

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

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

Jeffrey Coachman


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 Fye

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MyDanes

ASKER
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