Avatar of MyDanes
 asked on

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

"""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

2.  I need to extract the number AFTER the x


Thank you in advance!
Microsoft Access

Avatar of undefined
Last Comment

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
        fnSplit = Replace(split(SomeValue, SplitChar)(Element-1), chr$(34), "")

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
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
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

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