SQL to allow a "no preference (all) choice

[Rich Palarea]

One last question:

Since I would like to populate both MeetingLocation and GroupMix dropdowns
in the form from the data, how can I add the selection option of "Any" to
the list when the list is being created by a query?

Thanks (again)
RP

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

[Bob Barrows [MVP]]

If you don't want to use code to add the "Any" option (which is not that
hard to do), you can use a UNION query. If you show me one of the queries
and let me know where in the list you want the "Any" option to occur, I'll
provide an example ... unless someone else beats me to it. :-)

Bob Barrows

Rich Palarea wrote:

> One last question:
>
> Since I would like to populate both MeetingLocation and GroupMix
> dropdowns in the form from the data, how can I add the selection
> option of "Any" to the list when the list is being created by a query?
>
> Thanks (again)
> RP

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