String Parsing Query

[RH]

<SELECT name="txtServices" size="3" multiple>
<OPTION value="Massage">Massage</OPTION>
<OPTION value="Facials">Facials</OPTION>
<OPTION value="Wet & Dry Body Treatments">Wet &amp; Dry Body
Treatments</OPTION>
<OPTION value="Medical Treatments">Medical Treatments</OPTION>
<OPTION value="Nails, Pedicures, Manicures">Nails, Pedicures,
Manicures</OPTION>
<OPTION value="Hair">Hair</OPTION>
<OPTION value="Pools">Pools</OPTION>
<OPTION value="Waxing">Waxing</OPTION>
<OPTION value="Make-up">Make-up</OPTION>
<OPTION value="Aromatherapy">Aromatherapy</OPTION>
<OPTION value="Specialties">Specialties</OPTION>
<OPTION value="Tinting">Tinting</OPTION>
<OPTION value="Fitness & Nutritional Counselling">Fitness &amp;
Nutritional Counselling</OPTION>
</SELECT></TD>

Since this is a muliple select box the values are passed to the search
string comma delimited IE: facials, hair, pools etc

Can anyone tell me how to parse this string and then build a select
statement whereby I can search for Facials OR Hair OR pools
I've been searching all over and can't find anything that does just this.

Thanks in advance

 

[Steven Burn]

The really dirty method would be;

SomeString = "x, y, z"

Do While Not Instr(1, SomeString, ",", vbTextCompare)
MyString = StripIt(SomeString)
Response.Write MyString & "<br>"
Loop

Function StripIt(TheStirng)
Dim strTemp
strTemp = Split(TheString, ",")
StripIt = path(UBound(strTemp))
End Function

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[RosieO]

Thanks Curt - I've tried the second option, and while it works if you only
have one item selected to search for, it doesn't if you have 2.

Each field in the database only has one value, so one would be (Spa |
Services) 99 | hair, 99 | pools, 100 | hair, 100| Manicures etc.

If I select pools from the list, I get spa 99 & spa 100, If I select hair &
Pools, I should get spa 99 & spa 100

So, I've gone the other route.but what I can't figure out is how to make an
OR statement out of the array I get when I do Split.
(I've never done this before so I'm kindof confused with the syntax)

From what I can dig up information wise on this, I have to use a loop to
build an or statement that I can then pull into my query.

How do I do that??


Robyn

 

[RosieO]

Yes it does - should it then evaluate each piece separately as in an OR
statement?
Because if I just select one value, it works fine; if I select 2 it returns
nothing.

 

[TomB]

What if you just did.

SqlFieldName="Field1"
SomeString="x, y, z"
SomeString=SqlFieldName & " = '" & Replace(SomeString, ", ", "' OR " &
SqlFieldName & "='") & "'"

Which should produce

Field1='x' OR Field1='y' OR Field1='z'

 

[dlbjr]

SELECT ..... FROM ..... WHERE ........... IN ('" & Replace(yourValues ,",","','") & "')......."

'from dlbjr

'Unambit from meager knowledge of inane others,engender uncharted sagacity.

 

[TomB]

Ok. I feel dirty. How about.....

SomeString="x, y, z"

Dim arrVals
arrVals=Split(SomeString, ",")

Dim i
Dim sSQL
sSQL="SELECT Field1, Field2 FROM tblX WHERE "
for i=0 to UBound(arrVals)
if i>0 then
sSQL=sSQL & " OR "
end if
sSQL=sSQL & "Field1='" & arrVals(i) & "' "
next

 

[Steven Burn]

hehe, yep, that works too ;o)

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!