# 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

Microsoft Access

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:
"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
Dale Fye

Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
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.
MyDanes