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!
"""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!
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
first number will land in one field and the second will land in another field
Length x width
length = 1st num
width = 2nd num
2. Split([yourField], "x")(1)