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

[Curt_C [MVP]]

I suggest you take this one step at a time.
Look at Split() to break it apart
Look at a FOR loop to append the values.
OR
Just take the command delimited value and append it into the SQL directly
with a
"select.....where.....IN (" & yourValues & ")......"

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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!
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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

[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


"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:eC****************@TK2MSFTNGP11.phx.gbl...

I suggest you take this one step at a time.
Look at Split() to break it apart
Look at a FOR loop to append the values.
OR
Just take the command delimited value and append it into the SQL directly
with a
"select.....where.....IN (" & yourValues & ")......"

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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

[Curt_C [MVP]]

I thought that the multi select came throug comman-delimited, but I may be
wrong. You may have to do some simple Replace() calls to format it right

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RosieO" <Ro****@mail.com> wrote in message
news:u$**************@TK2MSFTNGP11.phx.gbl...

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


"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:eC****************@TK2MSFTNGP11.phx.gbl...

I suggest you take this one step at a time.
Look at Split() to break it apart
Look at a FOR loop to append the values.
OR
Just take the command delimited value and append it into the SQL directly

with a
"select.....where.....IN (" & yourValues & ")......"

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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

[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.

"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

I thought that the multi select came throug comman-delimited, but I may be
wrong. You may have to do some simple Replace() calls to format it right

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RosieO" <Ro****@mail.com> wrote in message
news:u$**************@TK2MSFTNGP11.phx.gbl...

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


"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:eC****************@TK2MSFTNGP11.phx.gbl...

I suggest you take this one step at a time.
Look at Split() to break it apart
Look at a FOR loop to append the values.
OR
Just take the command delimited value and append it into the SQL

directly with a
"select.....where.....IN (" & yourValues & ")......"

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...
> <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
>
>
>

[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'

"Steven Burn" <pv*@noyb.com> wrote in message
news:e$**************@TK2MSFTNGP12.phx.gbl...

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!
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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

[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" <pv*@noyb.com> wrote in message
news:e$**************@TK2MSFTNGP12.phx.gbl...

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!
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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]

hehe, yep, that works too ;o)

--

Regards

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

Keeping it FREE!
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:O8*************@TK2MSFTNGP11.phx.gbl...

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'

"Steven Burn" <pv*@noyb.com> wrote in message
news:e$**************@TK2MSFTNGP12.phx.gbl...

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!
"RH" <[e-mail]> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...

<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

[RosieO]

eureka!!!
I got it

If TServices<>"" then
strServices=replace(TServices, ", ", "','")
SqlString="SELECT display, category, name, addr1, addr2, city, provstate, country, postalzip, phone, fax, services FROM selectservices WHERE Services IN ('" & strServices & "') AND Display='"&tDisp&"' ORDER BY Name"

and works perfectly!!!!
Thanks so much!!

"RosieO" <Ro****@mail.com> wrote in message news:Og**************@tk2msftngp13.phx.gbl...

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.



"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:Od**************@TK2MSFTNGP10.phx.gbl...

I thought that the multi select came throug comman-delimited, but I may be
wrong. You may have to do some simple Replace() calls to format it right

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"RosieO" <Ro****@mail.com> wrote in message
news:u$**************@TK2MSFTNGP11.phx.gbl...

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


"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:eC****************@TK2MSFTNGP11.phx.gbl...
> I suggest you take this one step at a time.
> Look at Split() to break it apart
> Look at a FOR loop to append the values.
> OR
> Just take the command delimited value and append it into the SQL

directly

> with a
> "select.....where.....IN (" & yourValues & ")......"
>
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
>
> "RH" <[e-mail]> wrote in message
> news:ut**************@TK2MSFTNGP09.phx.gbl...
> > <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
> >
> >
> >
>
>