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 said:
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 said:
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
:
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
:
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
:
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
:
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
:
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
:
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