Add "All" to Combo Box and Use "All" as Criteria in Query

[meganrobertson22]

Hi Everyone-

I have a question about how to add and then use the "All" selection in
a combo box. I am trying to figure out how to:

(1) add "All" as a selection to a combo box and then

(2) how to use the selection "All" as criteria for a field in a
query, which is used to generate data for a report.
I use 3 different objects on a form as criteria for a query that is
used to generate a report. 2 of these objects are text boxes, and 1 is
a combo box.

I have a simple form named, frmTeam, which has: 2 text boxes, 1 combo
box, and 2 command buttons. The data from the 2 text boxes and combo
box are used as criteria in a query to generate a report.

The 2 text boxes are used to record a user-defined date range. The text
box named, txtBeginDate, stores the beginning date, and the text box
named, txtEndDate, stores the ending date. The dates from these 2 text
boxes are used in a "Between...And" statement as part of the
criteria in my query.

Between [forms]![frmTeam]![txtBeginDate] And
[forms]![frmTeam]![txtEndDate]
The combo box named, cboTeam, displays 2 fields from the table named,
Team. The combo box displays the fields: TeamID and TeamName. The 1st
column, TeamID, is the bound column. The selection in this combo box is
used, along with the 2 dates in the text boxes, as criteria in my
query.

[forms]![frmTeam]![cboTeam]
The 2 command buttons are very simple and straight forward. One command
button is used to preview my report, and the other command button
closes the form.
Right now, everything works fine. I select a Team, i.e. Atlanta Braves,
enter a Beginning Date (1/1/05) and an Ending Date (7/20/05), and my
query will list the names of all the team members that joined the team
between those 2 dates.

I want to add the selection "All: to my combo box so that I can see
all of the names of people who joined a team between those 2 dates.

For example: If I select "All" in my combo box and 1/1/05 as the
beginning date and 7/20/05 as my ending date, I want to see info such
as:

Atlanta Braves - John Doe
Atlanta Braves - Ray Park
New York Mets - Tom Jones
Texas Rangers - George Bush
I don't know how to correctly add the "All" selection to my combo
box and then use it as criteria for my query.
Tables:

Team
TeamID (Text & Primary Key)
TeamName

TeamMember
TeamMemberID (Text & Primary Key)
LastName
FirstName
JoinDate (Date)

Thanks,

Megan :-)

[pietlinden]

http://www.mvps.org/access/forms/frm0043.htm

[meganrobertson22]

i see how to add "all" to a combo box. but how do i use its value?

for instance:

my combo box displays: teamid | teamname from my table team.

teamid | teamname
1 atlanta braves
2 new york mets
3 florida marlins

whichever team i select, the combo box stores their primary key. if i
select "florida marlins" the value stored in my combo box is "3."

i then use this value of "3" as criteria in my query.

the query joins 2 tables: team and teammember.

the table team has:
teamid (pkl)
teamname

the table teammember has:
teammemberid (pk)
lastname
firstname
joindate (date)
teamid (fk to table team)
so when i select "florida marlins," my combo box saves "3" as its
value. "3" is then used in a query to limit the results.

questions:

if i add "all" to my combo box, what is its value?

how can i pass "all" to my query so that it returns all the teams?

i understand the union query i think, but how do i assign a value to
"all?"

thanks,

megan :-)

[John Welch]

you could write the query dynamically in code, and then only add the where
clause if teamid >0 or something.
or
your WHERE clause could be based on the teamname instead of the id, and then
you could base your where clause on "like" rather then "=". Then you could
put "*" in the combobox under teamname, and it would come out: WHERE
teamname like '*', giving you all teams.

I know these are not complete solutions, but they might give you some ideas.

<me**************@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

i see how to add "all" to a combo box. but how do i use its value?

for instance:

my combo box displays: teamid | teamname from my table team.

teamid | teamname
1 atlanta braves
2 new york mets
3 florida marlins

whichever team i select, the combo box stores their primary key. if i
select "florida marlins" the value stored in my combo box is "3."

i then use this value of "3" as criteria in my query.

the query joins 2 tables: team and teammember.

the table team has:
teamid (pkl)
teamname

the table teammember has:
teammemberid (pk)
lastname
firstname
joindate (date)
teamid (fk to table team)
so when i select "florida marlins," my combo box saves "3" as its
value. "3" is then used in a query to limit the results.

questions:

if i add "all" to my combo box, what is its value?

how can i pass "all" to my query so that it returns all the teams?

i understand the union query i think, but how do i assign a value to
"all?"

thanks,

megan :-)

[pietlinden]

One way to do this is to build your filter on the fly. Something like
this... NOT TESTED... so beware.

Sub cmdOpenReport '<== code behind your button to open your
report
strReport = me.cboReport

'--- if both date fields are filled in, add the date criteria
to the filter
if Not IsNull(me.dtStartDate) and Not IsNull(Me.dtEndDate)
Then
strFilter = "BETWEEN #" & dtStartDate & "# AND #" &
dtEndDate & "#"
end if

'---if the cboTeam combo is not ALL then DO add the filter,
otherwise, leave it off.
if cboTeam.columns(1)<>"All" then
strFilter = strFilter + " AND "
strFilter = strFilter & " Team = " & cboTeamID
end if

docmd.openreport "SomeReport", strFilter
end sub