SQL to allow a "no preference (all) choice

Rich Palarea · Rich Palarea

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.

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&Mee tingLocation=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&Mee tingLocation=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: [url]http://smallgroups.gfc.org/IDsite/locator.asp[/url]

Thanks!
Rich

Jeff Cochran · Jeff Cochran

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&Me etingLocation=202&GroupMix=
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&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&Me etingLocation=202&GroupMix=
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Conta
>ctHomePhone=202&ContactIndvEmail=202&"
>fp_iDisplayCols=11
>fp_fCustomQuery=True
>BOTID=0
>fp_iRegion=BOTID
>%>
>
>Present working page: [url]http://smallgroups.gfc.org/IDsite/locator.asp[/url]
>
>Thanks!
>Rich
>

Rich Palarea · Rich Palarea

Awesome, Jeff. I'm going to give it a try this weekend! Thanks.
Rich
"Jeff Cochran" <jeff.nospam@zina.com> wrote in message
news:40e7bfe7.630574928@msnews.microsoft.com...

> 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&Me etingLocation=202&GroupMix

=

>
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Cont

a

> >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&Me etingLocation=202&GroupMix

=

>
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Cont

a

> >ctHomePhone=202&ContactIndvEmail=202&"
> >fp_iDisplayCols=11
> >fp_fCustomQuery=True
> >BOTID=0
> >fp_iRegion=BOTID
> >%>
> >
> >Present working page: [url]http://smallgroups.gfc.org/IDsite/locator.asp[/url]
> >
> >Thanks!
> >Rich
> >

>

Rich Palarea · Rich Palarea

One more small item:

In my drop down for "MeetingLocation" - this is being dynamically populated by a call to the database to show all possible locations. This is done with SQL (below)

In the search page (where the populated drop down resides), what is the best way to add the drop down option of "Any" so that your SQL works?

Thanks,
Rich
++++++++++
SQL that populates the MeetingLocation drop down selection - sorry about all of the FrontPage junk!

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Smallgroups"
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="MeetingLocation"
fp_sMenuValue="MeetingLocation"
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&Mee tingLocation=202&GroupMix=202&MeetingTime=202&Chil drenAllowed=202&Status=202&ContactIndLabel=202&Con tactHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
<select NAME="MeetingLocation" SIZE="1">


<option><%=FP_FieldHTML(fp_rs,"MeetingLocation")%> </option>


</select>

"Jeff Cochran" <jeff.nospam@zina.com> wrote in message news:40e7bfe7.630574928@msnews.microsoft.com...

> 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&Me etingLocation=202&GroupMix=
> >202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&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&Me etingLocation=202&GroupMix=
> >202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Conta
> >ctHomePhone=202&ContactIndvEmail=202&"
> >fp_iDisplayCols=11
> >fp_fCustomQuery=True
> >BOTID=0
> >fp_iRegion=BOTID
> >%>
> >
> >Present working page: [url]http://smallgroups.gfc.org/IDsite/locator.asp[/url]
> >
> >Thanks!
> >Rich
> >

>

Rich Palarea · Rich Palarea

It almost works. I don't know enough about SQL syntax. It is probably
missing a single or double quote someplace. Here is the error message and
the SQL I'm using is below that:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(status = 'open' AND GroupMix = 'Any' AND
MeetingDay = 'Any'' AND MeetingLocation = 'Chase')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

SQL I'm using:

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

Thanks!
Rich
"Jeff Cochran" <jeff.nospam@zina.com> wrote in message
news:40e7bfe7.630574928@msnews.microsoft.com...

> 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&Me etingLocation=202&GroupMix

=

>
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Cont

a

> >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&Me etingLocation=202&GroupMix

=

>
>202&MeetingTime=202&ChildrenAllowed=202&Status=20 2&ContactIndLabel=202&Cont

a

> >ctHomePhone=202&ContactIndvEmail=202&"
> >fp_iDisplayCols=11
> >fp_fCustomQuery=True
> >BOTID=0
> >fp_iRegion=BOTID
> >%>
> >
> >Present working page: [url]http://smallgroups.gfc.org/IDsite/locator.asp[/url]
> >
> >Thanks!
> >Rich
> >

>

Bob Barrows [MVP] · Bob Barrows [MVP]

Rich Palarea wrote:

> It almost works. I don't know enough about SQL syntax. It is probably
> missing a single or double quote someplace. Here is the error
> message and the SQL I'm using is below that:
>
> Database Results Error
> Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error

Not related to this problem, but you should be using the Jet OLEDB provider
instead of ODBC. Google to see why.

> (missing operator) in query expression '(status = 'open' AND GroupMix
> = 'Any' AND MeetingDay = 'Any'' AND MeetingLocation = 'Chase')'.
> Number: -2147217900 (0x80040E14)
> Source: Microsoft OLE DB Provider for ODBC Drivers
>
> SQL I'm using:

<snip of vbscript statements>

That is not SQL. That is a series of vbscript statements designed to result
in a string containing a sql statment. Here is how you get the sql
statement:

Response.Write fp_sQry

Run the page and show us the statement that appears in the browser window.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bullschmidt · Bullschmidt

Don't know if this hopefully might give you some ideas:

Example on a page receiving a post of a SQL statement based on two
listboxes that might each say All to show all with no criteria
restrictions:

strSQL = "SELECT * MyTable WHERE (1=1)"
If MyFirstListBox <> "All" Then
strSQL = strSQL & " AND (MyType='" & Request.Form("MyFirstListBox") &
"')"
End If
If MySecondListBox <> "All" Then
strSQL = strSQL & " AND (MyDetail='" & Request.Form("MySecondListBox")
& "')"
End If

Example of a SQL statement based on a querystring:
strSQL = "SELECT * MyTable WHERE (MyDescription Like '%" &
Request.QueryString("DescriptionToSearch") & "%')"

Best regards,
J. Paul Schmidt, Freelance ASP Web Designer
[url]http://www.Bullschmidt.com[/url]
ASP Designer Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

*** Sent via Devdex [url]http://www.devdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

Rich Palarea · Rich Palarea

Revealing sql using Response.Write:

SELECT * FROM Smallgroups WHERE (status = 'open' AND GroupMix =
'::GroupMix::' AND MeetingDay = '::MeetingDay::'' AND MeetingLocation =
'::MeetingLocation::'')Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(status = 'open' AND GroupMix = 'Any' AND
MeetingDay = 'Any'' AND MeetingLocation = 'Chase'')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uHMb62rYEHA.212@TK2MSFTNGP12.phx.gbl...

> Rich Palarea wrote:

> > It almost works. I don't know enough about SQL syntax. It is probably
> > missing a single or double quote someplace. Here is the error
> > message and the SQL I'm using is below that:
> >
> > Database Results Error
> > Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error

>
> Not related to this problem, but you should be using the Jet OLEDB

provider

> instead of ODBC. Google to see why.
>

> > (missing operator) in query expression '(status = 'open' AND GroupMix
> > = 'Any' AND MeetingDay = 'Any'' AND MeetingLocation = 'Chase')'.
> > Number: -2147217900 (0x80040E14)
> > Source: Microsoft OLE DB Provider for ODBC Drivers
> >
> > SQL I'm using:

> <snip of vbscript statements>
>
> That is not SQL. That is a series of vbscript statements designed to

result

> in a string containing a sql statment. Here is how you get the sql
> statement:
>
> Response.Write fp_sQry
>
> Run the page and show us the statement that appears in the browser window.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Rich Palarea · Rich Palarea

On my server I use Jet OLEDB, but this is a customer's server...so we're
stuck with ODBC.

I fixed the issue - I removed two of the single quotes and it worked.

Rich

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uHMb62rYEHA.212@TK2MSFTNGP12.phx.gbl...

> Rich Palarea wrote:

> > It almost works. I don't know enough about SQL syntax. It is probably
> > missing a single or double quote someplace. Here is the error
> > message and the SQL I'm using is below that:
> >
> > Database Results Error
> > Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error

>
> Not related to this problem, but you should be using the Jet OLEDB

provider

> instead of ODBC. Google to see why.
>

> > (missing operator) in query expression '(status = 'open' AND GroupMix
> > = 'Any' AND MeetingDay = 'Any'' AND MeetingLocation = 'Chase')'.
> > Number: -2147217900 (0x80040E14)
> > Source: Microsoft OLE DB Provider for ODBC Drivers
> >
> > SQL I'm using:

> <snip of vbscript statements>
>
> That is not SQL. That is a series of vbscript statements designed to

result

> in a string containing a sql statment. Here is how you get the sql
> statement:
>
> Response.Write fp_sQry
>
> Run the page and show us the statement that appears in the browser window.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Bob Barrows [MVP] · Bob Barrows [MVP]

Rich Palarea wrote:

> Revealing sql using Response.Write:
>
> SELECT * FROM Smallgroups WHERE (status = 'open' AND GroupMix =
> '::GroupMix::' AND MeetingDay = '::MeetingDay::'' AND MeetingLocation
> = '::MeetingLocation::'')

Have you noticed the strange combination of quotes (") and apostrophes (')?
This definitely raises a flag in my mind. Why are you embedding quotes into
this string? If you start a string using ' for the delimiter, then you have
to finish with a '.

I'm also wondering how

status = 'open' AND GroupMix = '::GroupMix::'

became

status = 'open' AND GroupMix = 'Any'

as it appears in your error message. Move the Response.Write to immediately
before the line where you execute the string.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Rich Palarea · Rich Palarea

1. I got the code from Jeff in this thread. I pasted as-is, but had to
delete ' from two places for it to work.
2. status = 'open' is being submitted as a constant, but GroupMix,
MeetingDay and MeetingLocation are being submitted from drop down options in
a web form (.asp).

If you look over the whole thread, what I was trying to do was to accomodate
for users that wanted to see all choices in one or more of these options.
For example, they wanted to see all matches for any type of group
(GroupMix=Any) and didn't care about the MeetingDay (Any), but only met in a
specific loation (chosen from the list). Or any combination of these.

To see the form in context:
[url]http://smallgroups.gfc.org/IDsite/connect/locator.asp[/url]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23Tx3MItYEHA.3536@TK2MSFTNGP11.phx.gbl...

> Rich Palarea wrote:

> > Revealing sql using Response.Write:
> >
> > SELECT * FROM Smallgroups WHERE (status = 'open' AND GroupMix =
> > '::GroupMix::' AND MeetingDay = '::MeetingDay::'' AND MeetingLocation
> > = '::MeetingLocation::'')

>
> Have you noticed the strange combination of quotes (") and apostrophes

(')?

> This definitely raises a flag in my mind. Why are you embedding quotes

into

> this string? If you start a string using ' for the delimiter, then you

have

> to finish with a '.
>
> I'm also wondering how
>
> status = 'open' AND GroupMix = '::GroupMix::'
>
> became
>
> status = 'open' AND GroupMix = 'Any'
>
> as it appears in your error message. Move the Response.Write to

immediately

> before the line where you execute the string.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Bob Barrows [MVP] · Bob Barrows [MVP]

A simple way in sql is
(field = paramatervalue or parametervalue - 'any')

But dynamic sql is an optio, albeit dangerous from a security standpoint.

Bob Barrows

Rich Palarea wrote:

> 1. I got the code from Jeff in this thread. I pasted as-is, but had to
> delete ' from two places for it to work.
> 2. status = 'open' is being submitted as a constant, but GroupMix,
> MeetingDay and MeetingLocation are being submitted from drop down
> options in a web form (.asp).
>
> If you look over the whole thread, what I was trying to do was to
> accomodate for users that wanted to see all choices in one or more of
> these options. For example, they wanted to see all matches for any
> type of group (GroupMix=Any) and didn't care about the MeetingDay
> (Any), but only met in a specific loation (chosen from the list). Or
> any combination of these.
>
> To see the form in context:
> [url]http://smallgroups.gfc.org/IDsite/connect/locator.asp[/url]
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:%23Tx3MItYEHA.3536@TK2MSFTNGP11.phx.gbl...

>> Rich Palarea wrote:

>>> Revealing sql using Response.Write:
>>>
>>> SELECT * FROM Smallgroups WHERE (status = 'open' AND GroupMix =
>>> '::GroupMix::' AND MeetingDay = '::MeetingDay::'' AND
>>> MeetingLocation = '::MeetingLocation::'')

>>
>> Have you noticed the strange combination of quotes (") and
>> apostrophes (')? This definitely raises a flag in my mind. Why are
>> you embedding quotes into this string? If you start a string using '
>> for the delimiter, then you have to finish with a '.
>>
>> I'm also wondering how
>>
>> status = 'open' AND GroupMix = '::GroupMix::'
>>
>> became
>>
>> status = 'open' AND GroupMix = 'Any'
>>
>> as it appears in your error message. Move the Response.Write to
>> immediately before the line where you execute the string.
>>
>> Bob Barrows
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Rich Palarea · Rich Palarea

Bob - I had incorporated the <> "Any" option within my IF/THEN statement and
I added "Any" as a drop down value when "no preference" was selected from
the drop down menu.

Even though the page runs clean with no errors, the Any option is not
producing the desired results (which would be ALL records for that field,
but then filtered or pared down for subsequent choices prior to submit.

I'm missing something (more than my brain). I really like this stuff and I'm
learning. I appreciate your help.

Rich

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uGJLlUuYEHA.996@TK2MSFTNGP12.phx.gbl...

> A simple way in sql is
> (field = paramatervalue or parametervalue - 'any')
>
> But dynamic sql is an optio, albeit dangerous from a security standpoint.
>
> Bob Barrows
>
> Rich Palarea wrote:

> > 1. I got the code from Jeff in this thread. I pasted as-is, but had to
> > delete ' from two places for it to work.
> > 2. status = 'open' is being submitted as a constant, but GroupMix,
> > MeetingDay and MeetingLocation are being submitted from drop down
> > options in a web form (.asp).
> >
> > If you look over the whole thread, what I was trying to do was to
> > accomodate for users that wanted to see all choices in one or more of
> > these options. For example, they wanted to see all matches for any
> > type of group (GroupMix=Any) and didn't care about the MeetingDay
> > (Any), but only met in a specific loation (chosen from the list). Or
> > any combination of these.
> >
> > To see the form in context:
> > [url]http://smallgroups.gfc.org/IDsite/connect/locator.asp[/url]
> >
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:%23Tx3MItYEHA.3536@TK2MSFTNGP11.phx.gbl...

> >> Rich Palarea wrote:
> >>> Revealing sql using Response.Write:
> >>>
> >>> SELECT * FROM Smallgroups WHERE (status = 'open' AND GroupMix =
> >>> '::GroupMix::' AND MeetingDay = '::MeetingDay::'' AND
> >>> MeetingLocation = '::MeetingLocation::'')
> >>
> >> Have you noticed the strange combination of quotes (") and
> >> apostrophes (')? This definitely raises a flag in my mind. Why are
> >> you embedding quotes into this string? If you start a string using '
> >> for the delimiter, then you have to finish with a '.
> >>
> >> I'm also wondering how
> >>
> >> status = 'open' AND GroupMix = '::GroupMix::'
> >>
> >> became
> >>
> >> status = 'open' AND GroupMix = 'Any'
> >>
> >> as it appears in your error message. Move the Response.Write to
> >> immediately before the line where you execute the string.
> >>
> >> Bob Barrows
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"

>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Bob Barrows [MVP] · Bob Barrows [MVP]

Rich Palarea wrote:

> Bob - I had incorporated the <> "Any" option within my IF/THEN

No, you're not making the leap from "dynamic sql" to "parameter sql".

Try this search result:

[url]http://www.google.com/groups?as_q=saved%20parameter%20query&safe=images& ie=UTF-8&as_uauthors=Bob%20Barrows&lr=&hl=en[/url]

When you've read enough in these results to get a handle on what I mean by
"parameter query", then come back and ask how to handle an " or any"
situation.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Rich Palarea · Rich Palarea

OK, Bob. I'm all read up and set.

What you're saying is similar to using a stored procedure in the
client/server environment, right? Create a query and call that from your
..asp page instead of trying to query the table though vb and .asp. Seems
straightforward.

I have a new, saved query in Access that I called PQ_Smallgroups. At
present, it retrieves all records from the Smallgroups table.

Although I'm using JET on my own server, this site in question is a
customer's server that I'm authoring to and they are using ODBC. I've
supplied them with some info on why they need to switch, but for now, it is
ODBC.

Where do we go from here?

Thanks,
Rich

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O%23EfgquYEHA.2972@tk2msftngp13.phx.gbl...

> Rich Palarea wrote:

> > Bob - I had incorporated the <> "Any" option within my IF/THEN

>
> No, you're not making the leap from "dynamic sql" to "parameter sql".
>
> Try this search result:
>
>

[url]http://www.google.com/groups?as_q=saved%20parameter%20query&safe=images& ie=UTF-8&as_uauthors=Bob%20Barrows&lr=&hl=en[/url]

>
> When you've read enough in these results to get a handle on what I mean by
> "parameter query", then come back and ask how to handle an " or any"
> situation.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Bob Barrows [MVP] · Bob Barrows [MVP]

Rich Palarea wrote:

> OK, Bob. I'm all read up and set.
>
> What you're saying is similar to using a stored procedure in the
> client/server environment, right? Create a query and call that from
> your .asp page instead of trying to query the table though vb and
> .asp. Seems
> straightforward.
>
> I have a new, saved query in Access that I called PQ_Smallgroups. At
> present, it retrieves all records from the Smallgroups table.
>
> Although I'm using JET on my own server, this site in question is a
> customer's server that I'm authoring to and they are using ODBC. I've
> supplied them with some info on why they need to switch, but for now,
> it is
> ODBC.
>
> Where do we go from here?
>

So now you need to parameterize that saved query so you can pass parameter
values to it. I cover this in several of those articles I linked you to,
but, if you need me to get specific, you will need to show me some of the
sql in your saved query and describe how you want to filter the results.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jeff Cochran · Jeff Cochran

On Mon, 5 Jul 2004 18:57:48 -0400, "Rich Palarea" <nospam@none.com>
wrote:

>1. I got the code from Jeff in this thread. I pasted as-is, but had to
>delete ' from two places for it to work.

I should've told you it was not meant to run as is... :(

Never debugged, just typed. Made an assumption that from the snippet
I typed you'd be able to go back and rewrite your code to work, sorry
about that.

Jeff

Rich Palarea · Rich Palarea

The SQL out of Access looks like this:

SELECT Smallgroups.OrgID, Smallgroups.Name, Smallgroups.MeetingDay,
Smallgroups.MeetingLocation, Smallgroups.GroupMix, Smallgroups.MeetingTime,
Smallgroups.ChildrenAllowed, Smallgroups.Status,
Smallgroups.ContactIndLabel, Smallgroups.ContactHomePhone,
Smallgroups.ContactIndvEmail
FROM Smallgroups
WHERE (((Smallgroups.MeetingDay)=[Meeting Day:]) AND
((Smallgroups.MeetingLocation)=[Meeting Location:]) AND
((Smallgroups.GroupMix)=[Group Mix:]) AND ((Smallgroups.Status)="open"));

Because of the limited number of records currently in the database, we'd
like to allow users to open the filters up a bit by selecting "Any" for
MeetingDay, MeetingLocation and GroupMix.

MeetingDay are all of the days of the week (Sun-Sat), MeetingLocation are
cities that are read out of the database and populate a drop down menu in
the .asp page (using DISTINCT). GroupMix is a drop down populated the same
way as MeetingLocation.

Did I provide you with the correct info?

Thanks,
Rich

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OIbleL4YEHA.3128@TK2MSFTNGP09.phx.gbl...

> Rich Palarea wrote:

> > OK, Bob. I'm all read up and set.
> >
> > What you're saying is similar to using a stored procedure in the
> > client/server environment, right? Create a query and call that from
> > your .asp page instead of trying to query the table though vb and
> > .asp. Seems
> > straightforward.
> >
> > I have a new, saved query in Access that I called PQ_Smallgroups. At
> > present, it retrieves all records from the Smallgroups table.
> >
> > Although I'm using JET on my own server, this site in question is a
> > customer's server that I'm authoring to and they are using ODBC. I've
> > supplied them with some info on why they need to switch, but for now,
> > it is
> > ODBC.
> >
> > Where do we go from here?
> >

>
> So now you need to parameterize that saved query so you can pass parameter
> values to it. I cover this in several of those articles I linked you to,
> but, if you need me to get specific, you will need to show me some of the
> sql in your saved query and describe how you want to filter the results.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Bob Barrows [MVP] · Bob Barrows [MVP]

Rich Palarea wrote:

> The SQL out of Access looks like this:
>
> SELECT Smallgroups.OrgID, Smallgroups.Name, Smallgroups.MeetingDay,
> Smallgroups.MeetingLocation, Smallgroups.GroupMix,
> Smallgroups.MeetingTime, Smallgroups.ChildrenAllowed,
> Smallgroups.Status, Smallgroups.ContactIndLabel,
> Smallgroups.ContactHomePhone, Smallgroups.ContactIndvEmail
> FROM Smallgroups
> WHERE (((Smallgroups.MeetingDay)=[Meeting Day:]) AND
> ((Smallgroups.MeetingLocation)=[Meeting Location:]) AND
> ((Smallgroups.GroupMix)=[Group Mix:]) AND
> ((Smallgroups.Status)="open"));
>

My first suggestion is to get rid of the spaces and colons from the
parameter names. It's just my personal preference, but I like to avoid
non-alpha characters in my object names. It makes them easier to use in
code, and if you prefix them with "p", you'll never have any doubt you are
looking at a parameter.

My next suggestion is to use table aliases so you don't have to keep typing
and reading the names of the tables in your sql statement. Actually, since
there is a single table in your FROM clause, there is no need to clutter up
your query with aliases either.

So, after implementing the above suggestions, and eliinating the unnecessary
parentheses from your WHERE clause, the query looks like this:

SELECT OrgID, Name, MeetingDay,
MeetingLocation, GroupMix, MeetingTime,
ChildrenAllowed, Status,
ContactIndLabel, ContactHomePhone,
ContactIndvEmail
FROM Smallgroups
WHERE MeetingDay=[pMeetingDay] AND
MeetingLocation=[pMeetingLocation] AND
GroupMix=[pGroupMix]
AND Status="open"

> Because of the limited number of records currently in the database,
> we'd like to allow users to open the filters up a bit by selecting
> "Any" for MeetingDay, MeetingLocation and GroupMix.
>
> MeetingDay are all of the days of the week (Sun-Sat), MeetingLocation
> are cities that are read out of the database and populate a drop down
> menu in the .asp page (using DISTINCT). GroupMix is a drop down
> populated the same way as MeetingLocation.
>
> Did I provide you with the correct info?

Yes. Here is how to implement the "Any" filter:

WHERE
(MeetingDay= [pMeetingDay] OR [pMeetingDay] = "Any")
AND
(MeetingLocation= [pMeetingLocation] OR [pMeetingLocation] = "Any")
AND
(GroupMix= [pGroupMix] OR [pGroupMix] = "Any")
AND Status="open"

Make this change to your query and test it in Access, supplying the word
"Any" to one or more of the parameter prompts to verify that it works as
intended. In ASP, pass the word "Any" to the query for those parameters for
which the user chose "Any".

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Rich Palarea · Rich Palarea

It worked perfectly and I'm a new convert to doing it this way. So much
easier. Thank you.
Rich
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23s6fWI5YEHA.3420@TK2MSFTNGP12.phx.gbl...

> Rich Palarea wrote:

> > The SQL out of Access looks like this:
> >
> > SELECT Smallgroups.OrgID, Smallgroups.Name, Smallgroups.MeetingDay,
> > Smallgroups.MeetingLocation, Smallgroups.GroupMix,
> > Smallgroups.MeetingTime, Smallgroups.ChildrenAllowed,
> > Smallgroups.Status, Smallgroups.ContactIndLabel,
> > Smallgroups.ContactHomePhone, Smallgroups.ContactIndvEmail
> > FROM Smallgroups
> > WHERE (((Smallgroups.MeetingDay)=[Meeting Day:]) AND
> > ((Smallgroups.MeetingLocation)=[Meeting Location:]) AND
> > ((Smallgroups.GroupMix)=[Group Mix:]) AND
> > ((Smallgroups.Status)="open"));
> >

>
> My first suggestion is to get rid of the spaces and colons from the
> parameter names. It's just my personal preference, but I like to avoid
> non-alpha characters in my object names. It makes them easier to use in
> code, and if you prefix them with "p", you'll never have any doubt you are
> looking at a parameter.
>
> My next suggestion is to use table aliases so you don't have to keep

typing

> and reading the names of the tables in your sql statement. Actually, since
> there is a single table in your FROM clause, there is no need to clutter

up

> your query with aliases either.
>
> So, after implementing the above suggestions, and eliinating the

unnecessary

> parentheses from your WHERE clause, the query looks like this:
>
> SELECT OrgID, Name, MeetingDay,
> MeetingLocation, GroupMix, MeetingTime,
> ChildrenAllowed, Status,
> ContactIndLabel, ContactHomePhone,
> ContactIndvEmail
> FROM Smallgroups
> WHERE MeetingDay=[pMeetingDay] AND
> MeetingLocation=[pMeetingLocation] AND
> GroupMix=[pGroupMix]
> AND Status="open"
>
>

> > Because of the limited number of records currently in the database,
> > we'd like to allow users to open the filters up a bit by selecting
> > "Any" for MeetingDay, MeetingLocation and GroupMix.
> >
> > MeetingDay are all of the days of the week (Sun-Sat), MeetingLocation
> > are cities that are read out of the database and populate a drop down
> > menu in the .asp page (using DISTINCT). GroupMix is a drop down
> > populated the same way as MeetingLocation.
> >
> > Did I provide you with the correct info?

>
> Yes. Here is how to implement the "Any" filter:
>
> WHERE
> (MeetingDay= [pMeetingDay] OR [pMeetingDay] = "Any")
> AND
> (MeetingLocation= [pMeetingLocation] OR [pMeetingLocation] = "Any")
> AND
> (GroupMix= [pGroupMix] OR [pGroupMix] = "Any")
> AND Status="open"
>
>
> Make this change to your query and test it in Access, supplying the word
> "Any" to one or more of the parameter prompts to verify that it works as
> intended. In ASP, pass the word "Any" to the query for those parameters

for

> which the user chose "Any".
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

SQL to allow a "no preference (all) choice

Thread Tools

Display

SQL to allow a "no preference (all) choice

Similar Questions and Discussions

Any know how to avoid "Do you also want to uninstall Contribute Preference and configuration files for other users on this computer?"

Preference files

Help with preference file

Preference file

Where are preference files?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Posting Permissions