Creating a Dialog Box That Passes "All Records" as the Parameter

[nytwodees]

I am using Access 2000

I want to create a form with a Dialog Box that passes on 1 parameter to a
query. I want to select 1 of 7 different Departments to create a mailing
list for the Department selected. I also want the 1st item in the list to be
All Departments (if the user wants the entire mailing list - not just 1
particular department).

How do I pass the ALL Records (actually NO criteria) to the query so I can
achieve what I want?

Thanks in advance for your help!

 

[Allen Browne]

Craft the WHERE clause of your query so that it returns True if no value was
set.

Say your parameter is named WotDept. Switch your query to SQL View (view
menu), locate the WHERE clause, and set it up like this:
WHERE IIf([WotDept] Is Null, True, [DeptID] = [WotDept])

A better solution is to apply a filter to a form or report based on the
query. You can put an unbound combo on the form so the user can select the
department. If they leave it blank, you just don't apply a filter. There's
an example of a form with lots of filter boxes here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[nytwodees]

Thanks Allen for your prompt and thoughtful reply!

If I did not make myself clear, The selection area on the Dialog Box is a
Combo Box. The list on the Combo Box is to READ as follows:

All Departments
Accounting
Maintenance
Human Resources
etc.

I do NOT want the choice to be blank for All Departments. I'm trying to
make this as simple and consistent for the end user as possible.

Craft the WHERE clause of your query so that it returns True if no value was
set.

Say your parameter is named WotDept. Switch your query to SQL View (view
menu), locate the WHERE clause, and set it up like this:
WHERE IIf([WotDept] Is Null, True, [DeptID] = [WotDept])

A better solution is to apply a filter to a form or report based on the
query. You can put an unbound combo on the form so the user can select the
department. If they leave it blank, you just don't apply a filter. There's
an example of a form with lots of filter boxes here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2000

I want to create a form with a Dialog Box that passes on 1 parameter to a
query. I want to select 1 of 7 different Departments to create a mailing
list for the Department selected. I also want the 1st item in the list to
be
All Departments (if the user wants the entire mailing list - not just 1
particular department).

How do I pass the ALL Records (actually NO criteria) to the query so I can
achieve what I want?

Thanks in advance for your help!

 

[Douglas J. Steele]

In that case, change your WHERE to

WHERE ([DeptID] = Forms![NameOfForm]![NameOfCombo]
OR Forms![NameOfForm]![NameOfCombo] = "All Departments")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen for your prompt and thoughtful reply!

If I did not make myself clear, The selection area on the Dialog Box is a
Combo Box. The list on the Combo Box is to READ as follows:

All Departments
Accounting
Maintenance
Human Resources
etc.

I do NOT want the choice to be blank for All Departments. I'm trying to
make this as simple and consistent for the end user as possible.

Craft the WHERE clause of your query so that it returns True if no value
was
set.

Say your parameter is named WotDept. Switch your query to SQL View (view
menu), locate the WHERE clause, and set it up like this:
WHERE IIf([WotDept] Is Null, True, [DeptID] = [WotDept])

A better solution is to apply a filter to a form or report based on the
query. You can put an unbound combo on the form so the user can select
the
department. If they leave it blank, you just don't apply a filter.
There's
an example of a form with lots of filter boxes here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2000

I want to create a form with a Dialog Box that passes on 1 parameter to
a
query. I want to select 1 of 7 different Departments to create a
mailing
list for the Department selected. I also want the 1st item in the list
to
be
All Departments (if the user wants the entire mailing list - not just 1
particular department).

How do I pass the ALL Records (actually NO criteria) to the query so I
can
achieve what I want?

Thanks in advance for your help!

 

[nytwodees]

Hi Doug:

Thanks for your expert reply. Your solution worked perfectly on my 1st
attempt at running the code.

You would think that Access would have built-in code for this. I think what
I was trying to achieve is "common" or "everyday."

In that case, change your WHERE to

WHERE ([DeptID] = Forms![NameOfForm]![NameOfCombo]
OR Forms![NameOfForm]![NameOfCombo] = "All Departments")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen for your prompt and thoughtful reply!

If I did not make myself clear, The selection area on the Dialog Box is a
Combo Box. The list on the Combo Box is to READ as follows:

All Departments
Accounting
Maintenance
Human Resources
etc.

I do NOT want the choice to be blank for All Departments. I'm trying to
make this as simple and consistent for the end user as possible.

Craft the WHERE clause of your query so that it returns True if no value
was
set.

Say your parameter is named WotDept. Switch your query to SQL View (view
menu), locate the WHERE clause, and set it up like this:
WHERE IIf([WotDept] Is Null, True, [DeptID] = [WotDept])

A better solution is to apply a filter to a form or report based on the
query. You can put an unbound combo on the form so the user can select
the
department. If they leave it blank, you just don't apply a filter.
There's
an example of a form with lots of filter boxes here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am using Access 2000

I want to create a form with a Dialog Box that passes on 1 parameter to
a
query. I want to select 1 of 7 different Departments to create a
mailing
list for the Department selected. I also want the 1st item in the list
to
be
All Departments (if the user wants the entire mailing list - not just 1
particular department).

How do I pass the ALL Records (actually NO criteria) to the query so I
can
achieve what I want?

Thanks in advance for your help!