Designing Query - Help Please ASAP

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

It could be a flaw in the logic of the query's WHERE clause, but if you've
done as I described that should not be the case. Post back the SQL of the
query and I'll take a look.

Another possibility is that the bound column of one or more of the combo
boxes is not the visible column, but a hidden column. This is often the case
where the combo box lists text values from a column in a table whose primary
key column is a set of unique numeric values, frequently an autonumber. If
you post back the SQL of the RowSource properties of each combo box that
should give a clue as to whether this is a possibility here.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Thanks Ken,

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number])) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline Name])=[Forms]![frmProject
Staffing Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

--
tmdrake


"Ken Sheridan" wrote:

It could be a flaw in the logic of the query's WHERE clause, but if you've
done as I described that should not be the case. Post back the SQL of the
query and I'll take a look.

Another possibility is that the bound column of one or more of the combo
boxes is not the visible column, but a hidden column. This is often the case
where the combo box lists text values from a column in a table whose primary
key column is a set of unique numeric values, frequently an autonumber. If
you post back the SQL of the RowSource properties of each combo box that
should give a clue as to whether this is a possibility here.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Apologies for the delay in replying; I've been on the road. Lets see if we
can get this back to something in which the logic is more readily apparent:

SELECT ProjectID, [Discipline Name], [Section Number],
[Staff Last Name], [Staff First Name], [Discipline Lead],
[Est Project Start Date], [Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE
(ProjectID = [Forms]![frmProject Staffing Resources(1)]![ProjectID]
OR [Forms]![frmProject Staffing Resources(1)]![ProjectID] IS NULL)
AND
([Discipline Name] = [Forms]![frmProject Staffing Resources(1)]![Discipline
Name]
OR [Forms]![frmProject Staffing Resources(1)]![Discipline Name] IS NULL)
AND
([Section Number] = [Forms]![frmProject Staffing Resources(1)]![Section
Number]
OR [Forms]![frmProject Staffing Resources(1)]![Section Number] IS NULL);

I notice that in the SELECT clause you have a reference to a control
[Discipline Namer], but in the WHERE clause to [Discipline Name]. Is that
correct? Subject to any correction of the control name which might be needed
here, and subject to the caveats I expressed in my earlier reply regarding
the bound columns of the combo boxes, and hence their values, I can see no
reason why the above should not work.

Paste the above SQL into a new blank query in SQL view and save it while
still in SQL view to prevent Access recasting it if saved in design view.
Open the frmProject Staffing Resources(1) form and make some selections in
the combo boxes. Open the query in datasheet view and see what results you
get.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Thanks Ken,

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number])) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline Name])=[Forms]![frmProject
Staffing Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

--
tmdrake


"Ken Sheridan" wrote:

It could be a flaw in the logic of the query's WHERE clause, but if you've
done as I described that should not be the case. Post back the SQL of the
query and I'll take a look.

Another possibility is that the bound column of one or more of the combo
boxes is not the visible column, but a hidden column. This is often the case
where the combo box lists text values from a column in a table whose primary
key column is a set of unique numeric values, frequently an autonumber. If
you post back the SQL of the RowSource properties of each combo box that
should give a clue as to whether this is a possibility here.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

Hi Ken,

I am greatly appreciating your help.

Once I copied the before mentioned langauge in my SQL view, and re-ran the
query, It didn't perform as expected. You still have to make a choice from
the ProjectID combox for the selections from the remaining 2 boxes to
complete the Query. ProjectID is the only box that will work individually.

Also, when I looked at the SQL view of the query after makeing the
revisions, this in what it had. Is this suppose to change?
SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources (1)]![Discipline Name]

FROM [tblProject Staffing Resources]

WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]))

OR ((([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name]) AND
(([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null))

OR ((([tblProject Staffing Resources].[ProjectID])=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) Is Null))

OR ((([tblProject Staffing Resources].[Section
Number])=[Forms]![frmProject Staffing Resources(1)]![Section Number]) AND
(([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Name]) Is Null))

OR ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null))

OR ((([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name]) AND
(([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))

OR ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) Is Null)

AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is
Null)) OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Name]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

Again,
Thank you so much for your help!
--
tmdrake


"Ken Sheridan" wrote:

Apologies for the delay in replying; I've been on the road. Lets see if we
can get this back to something in which the logic is more readily apparent:

SELECT ProjectID, [Discipline Name], [Section Number],
[Staff Last Name], [Staff First Name], [Discipline Lead],
[Est Project Start Date], [Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE
(ProjectID = [Forms]![frmProject Staffing Resources(1)]![ProjectID]
OR [Forms]![frmProject Staffing Resources(1)]![ProjectID] IS NULL)
AND
([Discipline Name] = [Forms]![frmProject Staffing Resources(1)]![Discipline
Name]
OR [Forms]![frmProject Staffing Resources(1)]![Discipline Name] IS NULL)
AND
([Section Number] = [Forms]![frmProject Staffing Resources(1)]![Section
Number]
OR [Forms]![frmProject Staffing Resources(1)]![Section Number] IS NULL);

I notice that in the SELECT clause you have a reference to a control
[Discipline Namer], but in the WHERE clause to [Discipline Name]. Is that
correct? Subject to any correction of the control name which might be needed
here, and subject to the caveats I expressed in my earlier reply regarding
the bound columns of the combo boxes, and hence their values, I can see no
reason why the above should not work.

Paste the above SQL into a new blank query in SQL view and save it while
still in SQL view to prevent Access recasting it if saved in design view.
Open the frmProject Staffing Resources(1) form and make some selections in
the combo boxes. Open the query in datasheet view and see what results you
get.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Thanks Ken,

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number])) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline Name])=[Forms]![frmProject
Staffing Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

--
tmdrake


"Ken Sheridan" wrote:

It could be a flaw in the logic of the query's WHERE clause, but if you've
done as I described that should not be the case. Post back the SQL of the
query and I'll take a look.

Another possibility is that the bound column of one or more of the combo
boxes is not the visible column, but a hidden column. This is often the case
where the combo box lists text values from a column in a table whose primary
key column is a set of unique numeric values, frequently an autonumber. If
you post back the SQL of the RowSource properties of each combo box that
should give a clue as to whether this is a possibility here.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks
--
tmdrake

The changes made to the SQL of the query result from switching into design
view rather than staying in SQL view. The logic is fundamentally the same
but Access reorganises things in such a way that its possible to represent
the query in design view with each individual criterion contained
independently of the others in a single row of a column in the design grid.
The Boolean logic is represented in design view by having criteria on the
same row for an AND operation, on a separate row for an OR operation. When
this is then expressed in SQL the result is an overly complex logical
structure rather than the simple easily read one which writing the query
directly in SQL produces. It should work the same both ways, but my advice
is to design the query entirely in SQL view and save it as such in order to
preserve the simpler and intelligible logical structure.

I suspect the reason why you are not getting the results you expect is to do
with the combo boxes not the query. It may be that when you leave one blank
it is not in fact Null but a zero-length string, or it might be due to the
value being a hidden column rather than the visible one as I suggested
before. If a combo box's RowSource query returns two columns rather than
just the one you see in the control then the latter is likely to be the case.
If so then it might be that the control has a default value of zero, in
which case it would appear blank but would not Null.

To pin this down I think you will need to do some debugging of the form to
see exactly what the value of each combo box is at any one time. Don't
assume that what you see in the control is necessarily its value, or that if
it is blank it is Null. The Project ID combo box looks like the prime
suspect as if it works when a selection is made in this but the other two are
blank then it suggests that the others are in fact Null as expected.
Conversely the fact that it does not work correctly if the Project ID is
blank suggests that it is not Null.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

I am greatly appreciating your help.

Once I copied the before mentioned langauge in my SQL view, and re-ran the
query, It didn't perform as expected. You still have to make a choice from
the ProjectID combox for the selections from the remaining 2 boxes to
complete the Query. ProjectID is the only box that will work individually.

Also, when I looked at the SQL view of the query after makeing the
revisions, this in what it had. Is this suppose to change?
SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources (1)]![Discipline Name]

FROM [tblProject Staffing Resources]

WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]))

OR ((([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name]) AND
(([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null))

OR ((([tblProject Staffing Resources].[ProjectID])=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) Is Null))

OR ((([tblProject Staffing Resources].[Section
Number])=[Forms]![frmProject Staffing Resources(1)]![Section Number]) AND
(([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Name]) Is Null))

OR ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null))

OR ((([tblProject Staffing Resources].[Discipline
Name])=[Forms]![frmProject Staffing Resources(1)]![Discipline Name]) AND
(([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))

OR ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) Is Null)

AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is
Null)) OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Name]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

Again,
Thank you so much for your help!
--
tmdrake


"Ken Sheridan" wrote:

Apologies for the delay in replying; I've been on the road. Lets see if we
can get this back to something in which the logic is more readily apparent:

SELECT ProjectID, [Discipline Name], [Section Number],
[Staff Last Name], [Staff First Name], [Discipline Lead],
[Est Project Start Date], [Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE
(ProjectID = [Forms]![frmProject Staffing Resources(1)]![ProjectID]
OR [Forms]![frmProject Staffing Resources(1)]![ProjectID] IS NULL)
AND
([Discipline Name] = [Forms]![frmProject Staffing Resources(1)]![Discipline
Name]
OR [Forms]![frmProject Staffing Resources(1)]![Discipline Name] IS NULL)
AND
([Section Number] = [Forms]![frmProject Staffing Resources(1)]![Section
Number]
OR [Forms]![frmProject Staffing Resources(1)]![Section Number] IS NULL);

I notice that in the SELECT clause you have a reference to a control
[Discipline Namer], but in the WHERE clause to [Discipline Name]. Is that
correct? Subject to any correction of the control name which might be needed
here, and subject to the caveats I expressed in my earlier reply regarding
the bound columns of the combo boxes, and hence their values, I can see no
reason why the above should not work.

Paste the above SQL into a new blank query in SQL view and save it while
still in SQL view to prevent Access recasting it if saved in design view.
Open the frmProject Staffing Resources(1) form and make some selections in
the combo boxes. Open the query in datasheet view and see what results you
get.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Thanks Ken,

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].[Discipline Name], [tblProject Staffing Resources].[Section
Number], [tblProject Staffing Resources].[Staff Last Name], [tblProject
Staffing Resources].[Staff First Name], [tblProject Staffing
Resources].[Discipline Lead], [tblProject Staffing Resources].[Est Project
Start Date], [tblProject Staffing Resources].[Est Project End Date],
[Forms]![frmProject Staffing Resources(1)]![Discipline Namer]
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID]) AND (([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number])) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Section Number])=[Forms]![frmProject
Staffing Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].[Section Number])=[Forms]![frmProject Staffing
Resources(1)]![Section Number]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Discipline Namer]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].[Discipline Name])=[Forms]![frmProject
Staffing Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].[Discipline Name])=[Forms]![frmProject Staffing
Resources(1)]![Discipline Name]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![Section Number]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Discipline Namer]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![Section Number]) Is Null));

--
tmdrake


"Ken Sheridan" wrote:

It could be a flaw in the logic of the query's WHERE clause, but if you've
done as I described that should not be the case. Post back the SQL of the
query and I'll take a look.

Another possibility is that the bound column of one or more of the combo
boxes is not the visible column, but a hidden column. This is often the case
where the combo box lists text values from a column in a table whose primary
key column is a set of unique numeric values, frequently an autonumber. If
you post back the SQL of the RowSource properties of each combo box that
should give a clue as to whether this is a possibility here.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Hi Ken,

Your assistance is greatly appreciated. However, I am simply not getting
it. I did what you suggested, but in I can still not use the combox
selection individually.

If I make a selection in the ProjectID box (1st box) = then the subform will
populate.

In order to select by Discipline Name - I have to choose a selection from
all combo boxes.

If I make a selection in the Discipline Name box (2nd box) nothing happens
unless I make a selection in the ProjectID box and the Section Number box;
hit Search and the subform populates.

In other words making individual selections in the 2nd and 3rd boxes will
only work if I make a choice in the first box.

Hopefully,

I am not confusing you as much as I am confusing myself.

Again Thanks for you help.
--
tmdrake


"Ken Sheridan" wrote:

In design view you'd enter each of the OR expressions in the first criteria
line of each column respectively, so for the Project ID column you'd put:

Forms![YourForm]![ComboBox 1] OR Forms![YourForm]![ComboBox 1] IS NULL

For the Discipline Name column:

Forms![YourForm]![ComboBox 2] OR Forms![YourForm]![ComboBox 2] IS NULL

And for the Section name column:

Forms![YourForm]![ComboBox 3] OR Forms![YourForm]![ComboBox 3] IS NULL

YourForm should be replaced with the name of your form of course.

When you put criteria in a query all that's required for a row to be
returned is for the criteria expression (in SQL the query's WHERE clause) as
a whole to evaluate to TRUE. What you have in this case are three
expressions, one for each column which use a Boolean OR operation, so if the
value in the control matches the value in a row OR the control is blank
(NULL) then the individual expression will evaluate to TRUE. Each of the
three expressions are part of the overall expression which uses two Boolean
AND operations (its easier to see the logic in the SQL rather than in design
view).

So all three of the individual expressions must evaluate to TRUE for the
whole expression to evaluate to TRUE and a row to be returned. What this
does in effect is to make each parameter optional, so a user can enter values
in none, one, two or three of them and the query will return rows based on
the combination of whichever controls the user has entered values in or left
blank. If only one is entered then rows which match that value will be
returned, if two are entered then rows which match both those values will be
returned, and so on. If none are entered then all rows will be returned.

You'll find that if you save the query and open it again in design view
Access will have moved things around. It will work just the same however.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

Where do I reference combox's on Query?, In the Criteria field on the query
grid. I don't mean to be a pest, but could you exlplain what you did step by
step.

Thanks you very much, your help is greatly appreciates.
tmdrake


"Ken Sheridan" wrote:

Reference the combo boxes as parameters in the subform's underlying query,
testing each for IS NULL in parenthesised Boolean OR operations, e.g.

SELECT *
FROM [YourTable]
WHERE
([Project ID] = Forms![YourForm]![ComboBox 1]
OR Forms![YourForm]![ComboBox 1] IS NULL)
AND ([Discipline Name] = Forms![YourForm]![ComboBox 2]
OR Forms![YourForm]![ComboBox 2] IS NULL)
AND ([Section Name] = Forms![YourForm]![ComboBox 3]
OR Forms![YourForm]![ComboBox 3] IS NULL);

In the button's Click event procedure requery the subform:

Me.YourSubformControl.Requery

Where YourForm is the name of the main parent form and YourSubformControl is
the name of the control on the main parent form which houses the subform.

Ken Sheridan
Stafford, England

"tmdrake" wrote:

I have built a form with (3) combox's that display different selections. On
that form I have a command button. I would like to built a query based on the
selections from the combox and the results are placed in the subform.

Example.
Combox 1 - Project ID (list the ID's for different projects)
Combox 2 - Discipline Name (list the different discipline names)
Combox 3 - Section Number (list the different section numbers)

Based on the combox box selected 1 or 2 3 or 1,2 and 3 (or multiple
selections) the command button runs a query that filters for the selection
and then displays the query results in my subform.

Will some one please help me with this problem.

Thanks