"Michael Kujawa" <nof at kujawas dot net> wrote in message news:uuUbAAV7GHA.2384@TK2MSFTNGP04.phx.gbl...
>I am using the following to create an SQL statement
> using the names and values from request.form.
> The loop goes through each item in request.form
>
> The issue comes in having an additional "and" at the end
> of the loop and the value of x3 as not all options may be selected
> from the form yet the loop goes through the entire request.form list
>
> I have to add addtional code to strip off the last "and"
> and was wondering if there is a way to eliminate the additional
> code needed for stripping the "and".
>
> Is there a way to determine which is the very last request.form.item(i)
> that has a .key value and use that as x3?  I would imagine I would not
> need the additional code if there is a way to determine this.
>
> Any help is appreciated
>
>
> for j = 2 to Request.Form.count()
> x2= x2 & request.form.item(j)
> next
> if len(rtrim(x2)) = 0 then
> response.write "no options chosen"
> else
> whereby= ""
> x3=Request.Form.count
> for i = 2 to Request.Form.count()
>  if len(trim(request.form(i))) > 0 then
>   if request.form.key(i) = "MFR" then
>   whereby = whereby & " (Mfg1='" & request.form.item(i) & "' or mfg2='" &
> request.form.item(i) & "' or mfg3='" & request.form.item(i) & "') AND "
>   elseif request.form.key(i) <> "MFR" then whereby = whereby &
> trim(request.form.key(i)) & "='" & Request.Form.item(i) & "' AND "
>   elseif i=x3 then whereby = whereby & trim(request.form.key(i)) & "='" &
> Request.Form.item(i) & "' "
>   end if
>  end if
> next
>
> ' Needed to strip last AND from statement
> x4=mid(rtrim(whereby),len(rtrim(whereby))-3,4)
> if trim(x4) = "AND" then
> whereby = mid(rtrim(whereby),1,len(rtrim(whereby))-3) & "order by rec_no "
> & Updown
> else
> whereby=whereby & "order by rec_no " & Updown
> end if
> ' End additional Code
>
> sql = sql & whereby
>
>

"Jon Paal" <Jon nospam Paal @ everywhere dot com> wrote in message
news:12iq97anjg88l13@corp.supernews.com...
> if j= Request.Form.count() then
>     whereby = whereby & ...
> else
>     whereby = whereby & ... & "AND"
> end if
>
>
> "Michael Kujawa" <nof at kujawas dot net> wrote in message
news:uuUbAAV7GHA.2384@TK2MSFTNGP04.phx.gbl...
> >I am using the following to create an SQL statement
> > using the names and values from request.form.
> > The loop goes through each item in request.form
> >
> > The issue comes in having an additional "and" at the end
> > of the loop and the value of x3 as not all options may be selected
> > from the form yet the loop goes through the entire request.form list
> >
> > I have to add addtional code to strip off the last "and"
> > and was wondering if there is a way to eliminate the additional
> > code needed for stripping the "and".
> >
> > Is there a way to determine which is the very last request.form.item(i)
> > that has a .key value and use that as x3?  I would imagine I would not
> > need the additional code if there is a way to determine this.
> >
> > Any help is appreciated
> >
> >
> > for j = 2 to Request.Form.count()
> > x2= x2 & request.form.item(j)
> > next
> > if len(rtrim(x2)) = 0 then
> > response.write "no options chosen"
> > else
> > whereby= ""
> > x3=Request.Form.count
> > for i = 2 to Request.Form.count()
> >  if len(trim(request.form(i))) > 0 then
> >   if request.form.key(i) = "MFR" then
> >   whereby = whereby & " (Mfg1='" & request.form.item(i) & "' or mfg2='"
&
> > request.form.item(i) & "' or mfg3='" & request.form.item(i) & "') AND "
> >   elseif request.form.key(i) <> "MFR" then whereby = whereby &
> > trim(request.form.key(i)) & "='" & Request.Form.item(i) & "' AND "
> >   elseif i=x3 then whereby = whereby & trim(request.form.key(i)) & "='"
&
> > Request.Form.item(i) & "' "
> >   end if
> >  end if
> > next
> >
> > ' Needed to strip last AND from statement
> > x4=mid(rtrim(whereby),len(rtrim(whereby))-3,4)
> > if trim(x4) = "AND" then
> > whereby = mid(rtrim(whereby),1,len(rtrim(whereby))-3) & "order by rec_no
"
> > & Updown
> > else
> > whereby=whereby & "order by rec_no " & Updown
> > end if
> > ' End additional Code
> >
> > sql = sql & whereby
> >
> >
>
>

"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:%23b4xC4V7GHA.3604@TK2MSFTNGP02.phx.gbl...
>
>
> > >
> > > "Michael Kujawa" <nof at kujawas dot net> wrote in message
> > news:uuUbAAV7GHA.2384@TK2MSFTNGP04.phx.gbl...
>
> > > > Is there a way to determine which is the very last
> request.form.item(i)
> > > > that has a .key value and use that as x3?  I would imagine I would
not
> > > > need the additional code if there is a way to determine this.
> > > >
> > > > Any help is appreciated
> > > >
>
> ">
> > "Jon Paal" <Jon nospam Paal @ everywhere dot com> wrote in message
> > news:12iq97anjg88l13@corp.supernews.com...
> > > if j= Request.Form.count() then
> > >     whereby = whereby & ...
> > > else
> > >     whereby = whereby & ... & "AND"
> > > end if
> > >
>
> Sorry My Bad... I forgot "No top posting"
>
> Hi Jon,
>
> That will not work. I would get the same results as
> the request.form.count() returns the entire set.
>
> I need to know how to find the last .item with a value
> and use that for the x3 value, instead of  request.form.count().
> Otherwise the additional AND is still there.
>
> Not sure how to do that.
>
> for example, if I have 22 elements in the form and the last
> element chosen is 20 then I need to know that for the value
> of  for i = 2 to request.form.(?)
>
> count gives the entry set and that is where the issue lies.
> The form is a series of checkboxes and dropdown lists
> and requires only one item to be chosen, but can have up
> to 22 selections.
>
>