Re: SQL to allow a "no preference (all) choice

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jeff Cochran (jeff.nospam_at_zina.com)
Date: 07/02/04 

Date: Fri, 02 Jul 2004 20:17:45 GMT

On Fri, 2 Jul 2004 14:41:20 -0400, "Rich Palarea" <nospam@none.com>
wrote:

>I have an Access db and a set of .asp pages.
>
>I'd like to offer a website visitor the chance to select all records, in
>addition to being able to select the preferences they want (i.e.: no
>preference). To give you a better idea of how the sql is structured (as you
>can see, it is FrontPage generated sql, but can be edited with standard SQL
>commands):
>
>User can choose to find a group by three fields: GroupMix, MeetingDay,
>MeetingLocation from the table Smallgroups. All are dropdown fields in a
>form. GroupMix and MeetingLocation are populated by a SELECT statement to
>the Smallgroups table. MeetingDay is pre-populated with days of the week.
>
>It is easy to get "no records found" with this setup because of the limited
>number of records in the table at present. I would like the user to be
>allowed the choices of "no preference" inside of the three drop down fields.

Make "Any" an option in your drop down, then use IF/THEN statements or
CASE to build a WHERE clause in your query. Something like (watch for
code wrap):

----------------------------------------------------------
fp_sQry = "SELECT * FROM Smallgroups"
IF GroupMix <> "Any" OR MeetingDay <> "Any" OR MeetingLocation <>
"Any" THEN
  fp_sQry = fp_sQry & " WHERE (status = 'open'"
  IF GroupMix <> "Any" THEN
    fp_sQry = fp_sQry & " AND GroupMix = '::GroupMix::'"
  END IF
  IF MeetingDay <> "Any" THEN
    fp_sQry = fp_sQry & " AND MeetingDay = '::MeetingDay::''"
  END IF
  IF MeetingLocation <> "Any" THEN
    fp_sQry = fp_sQry & " AND MeetingLocation =
'::MeetingLocation::''"
  END IF
  fp_sQry = fp_sQry & ")"
END IF
----------------------------------------------------------

Jeff

>Present SQL for the form (showing just the dropdown portion for GroupMix;
>there is a similar select for MeetingLocation further down in my page):
>
><% end if %>
><%
>fp_sQry="SELECT Distinct Smallgroups.GroupMix FROM Smallgroups ORDER BY
>GroupMix ASC"
>fp_sDefault=""
>fp_sNoRecords="No records returned."
>fp_sDataConn="GFCData"
>fp_iMaxRecords=256
>fp_iCommandType=1
>fp_iPageSize=0
>fp_fTableFormat=False
>fp_fMenuFormat=True
>fp_sMenuChoice="GroupMix"
>fp_sMenuValue="GroupMix"
>fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=
>202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&Conta
>ctHomePhone=202&ContactIndvEmail=202&"
>fp_iDisplayCols=1
>fp_fCustomQuery=False
>BOTID=0
>fp_iRegion=BOTID
>%>
>
>Present SQL for the results page:
><% end if %>
><%
>fp_sQry="SELECT * FROM Smallgroups WHERE (status = 'open' AND MeetingDay =
>'::MeetingDay::' AND GroupMix = '::GroupMix::' AND MeetingLocation =
>'::MeetingLocation::')"
>fp_sDefault=""
>fp_sNoRecords="No records returned."
>fp_sDataConn="GFCData"
>fp_iMaxRecords=256
>fp_iCommandType=1
>fp_iPageSize=0
>fp_fTableFormat=False
>fp_fMenuFormat=False
>fp_sMenuChoice=""
>fp_sMenuValue=""
>fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=
>202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&Conta
>ctHomePhone=202&ContactIndvEmail=202&"
>fp_iDisplayCols=11
>fp_fCustomQuery=True
>BOTID=0
>fp_iRegion=BOTID
>%>
>
>Present working page: http://smallgroups.gfc.org/IDsite/locator.asp
>
>Thanks!
>Rich
>

Relevant Pages

  • SQL to allow a "no preference (all) choice
    ... can see, it is FrontPage generated sql, but can be edited with standard SQL ... User can choose to find a group by three fields: GroupMix, MeetingDay, ... MeetingLocation from the table Smallgroups. ... GroupMix and MeetingLocation are populated by a SELECT statement to ...
    (microsoft.public.inetserver.asp.db)
  • Re: SQL to allow a "no preference (all) choice
    ... MeetingDay are all of the days of the week, MeetingLocation are ... it retrieves all records from the Smallgroups table. ... > sql in your saved query and describe how you want to filter the results. ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: SQL to allow a "no preference (all) choice
    ... In the search page, what is the best way to add the drop down option of "Any" so that your SQL works? ... SQL that populates the MeetingLocation drop down selection - sorry about all of the FrontPage junk! ... >>MeetingLocation from the table Smallgroups. ...
    (microsoft.public.inetserver.asp.db)
  • Allowing a visitor a "no preference" choice
    ... User can choose to find a group by three fields: GroupMix, MeetingDay, ... MeetingLocation from the table Smallgroups. ... GroupMix and MeetingLocation are populated by a SELECT statement to ... allowed the choices of "no preference" inside of the three drop down fields. ...
    (microsoft.public.frontpage.programming)