How Do I isolate specific text in Access?

[alex]

Hi,

I have a problem. I need to isolate or for lack of a better word separate
specific text (I would like to separate the size and bottles) in a
description, but since the product description changes in length and position
I don’t know how to separate this information in a query in Access. Can
anyone help??
Below is an example of what I mean and what I need to do.

Problem:

Description I NEED ------------> Btls Per Cs Size

Jack Daniel’s 6/750ml 6 750
Jack Daniel’s 12/750ml 12 750
Jack Daniel’s 1/3L 1 3L
Jack Daniel’s 6/750ml gift pack 6 750

 

[Tom van Stiphout]

On Fri, 6 Nov 2009 07:01:01 -0800, alex

Do you mean you currently have a field that includes the description,
bottles, and size, all in one field? If so, you need to find a
suitable separator between those parts, and Split on that. There may
be a tab character, or at least 2 spaces, or ...
The Split function will return an array of elements, which you can
Trim if needed, and save to different fields.
Or go back to the source of the data and ask them to supply it to you
in a better format.

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

Well IF (big IF) there is always a / after the number that you need and IF
(there it is again) the number is less that 100, the following MIGHT work.

Btls Per Cs: Val(Mid([Description],InStr([Description], "/")-2))

 

[Dale Fye]

If you need to do this in a query, it will be quite complicated. It looks
like you could search for the "/" then look for the last space before that
character and the first space after that character. It would probably be
easier to do it in a function:

Public Function BottlesAndSize(Description As Variant, Part As String) As
Variant

Dim charPosn As Integer, spPosn As Integer

If IsNull(Description) Then
BottlesAndSize = Null
Exit Function
End If

charPosn = InStrRev(Description, "/")
If charPosn = 0 Then
BottlesAndSize = Null
Exit Function
End If

If Part = "Bottles" Then
spPosn = InStrRev(Description, " ", charPosn - 1)
If spPosn = 0 Then
BottlesAndSize = Null
Else
BottlesAndSize = Mid(Description, spPosn + 1, charPosn - spPosn
- 1)
End If
Else
spPosn = InStr(charPosn + 1, Description, " ")
If spPosn = 0 Then
BottlesAndSize = Mid(Description, charPosn + 1)
Else
BottlesAndSize = Mid(Description, charPosn + 1, spPosn -
charPosn - 1)
End If
End If

End Function