query value between COMMAS

[dastr]

Hi all,
I have the following data:
Article Sizes
Shoes 40,41,42,43
Tshirt s,m,x,xl
Jeans 30,32,34

I was wondering is there any query that would easily give me the size between the SECOND and the THIRD comma – for shoes – 41; for tshirt – M; for jeans – 32?
Any help will be highly appreciated,
Thx

 

[Trevor G]

Read this artical and check out the InStr Function

http://custom-software.biz/AccessWizard/Dec04.htm

 

[dastr]

Read this artical and check out the InStr Function

http://custom-software.biz/AccessWizard/Dec04.htm

Hi Trevor,

I am trying to figure out how to get the single value, not everything after the first comma. Thank you

 

[mahenkj2]

But then you have things to experiment with, you have Left, right, Len and InStr functions. What problem you are facing.

 

[pbaldy]

For starters, your examples are between the first and second commas, not second and third. In any case check out the Split() function.

 

[lutajuca]

One of the many ways:

Function fSecondValue(ByVal Origin As String, ByVal separator As String) As String
  fSecondValue = Mid(Origin,[COLOR=Red] InStr(1, Origin, separator)[/COLOR] + 1, _
  [COLOR=Blue]InStr(InStr(1, Origin, separator) + 1, Origin, separator)[/COLOR] - _
  InStr(1, Origin, separator) - 1)
End Function

InStr(1, Origin, separator) position of first comma
InStr(InStr(1, Origin, separator) + 1, Origin, separator) position of second comma

RequiredValue = fSecondValue(Sizes,",")

 

[Guus2005]

Like pbaldy said, the split command does the work in a single line:

?split("Shoes 40,41,42,43",",")(1)

returns 41

if you want the shoes go first:

?split("Shoes 40,41,42,43"," ")(0) & " - "& split("Shoes 40,41,42,43",",")(1)

returns Shoes - 41

HTH

 

[lutajuca]

More elegant ... if you know arrays.....