Access is compiling query results rather than creating a new set

[christopher.miles.quinn]

I have created a query which displays the results in a list box.

Within this query there are a couple of fields which criteria is
linked to combo boxes.

When a user selects an option witihin the combo box, (on change) it
requerys the list box which refreshes the query.

However, I have recently added some if statements into the query,
which also has criteria linked to a combo box. Which I want to run
either/or not together

The first time I select something in the combo box, it works perfectly
and the list box displays the correct results. But then when I change
the selection in the combo box, both criteria from the previous and
current selection are used by the query. Giving me the wrong results.

Can anyone explain what I am doing wrong

Thanks,
Chris

 

[Jeff Boyce]

It would probably help if we could see the SQL statement(s) you're using...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[christopher.miles.quinn]

Sorry the SQL View in the query is as follows:

SELECT tblProject.ProjectID, tblProject.BC, tblProject.RT,
tblProject.SC, tblProject.KN, tblProject.GR, tblProject.WT,
tblProject.PE, tblProject.HB, tblProject.RH, tblProject.CQ,
tblProject.JF, tblProject.StatusID, tblProject.PriorityID,
tblCategory.CategoryID, tblProject.Software1, tblProject.Software2,
tblProject.Software3, tblProject.Software4, tblUnit.BusinessUnit,
tblProject.ProjectName, tblProject.DateReceived,
tblProject.DateStarted, tblProject.DateDue, tblProject.Completed,
tblProject.DateCompleted, tblProject.Comments, tblProject.DateEntered,
tblPriority.Priority, tblStatus.Status, tblCategory.Category
FROM tblCategory RIGHT JOIN (tblUnit RIGHT JOIN (tblStatus RIGHT JOIN
(tblPriority RIGHT JOIN tblProject ON
tblPriority.PriorityID=tblProject.PriorityID) ON
tblStatus.StatusID=tblProject.StatusID) ON
tblUnit.UnitID=tblProject.UnitID) ON
tblCategory.CategoryID=tblProject.CategoryID
WHERE (((tblProject.BC)=IIf(Forms![PRSU Project Log]!
SearchTeam='Bernard Cook',1,(tblProject.BC) Is Not Null)) And
((tblProject.PE)=IIf(Forms![PRSU Project Log]!SearchTeam='Paul Emuss',
1,(tblProject.PE) Is Not Null)) And ((tblProject.StatusID) Like "*" &
Forms![PRSU Project Log]!SearchStatus & "*") And
((tblProject.PriorityID) Like "*" & Forms![PRSU Project Log]!
SearchPriority & "*") And ((tblCategory.CategoryID) Like "*" & Forms!
[PRSU Project Log]!SearchCategory & "*") And ((tblUnit.BusinessUnit)
Like "*" & Forms![PRSU Project Log]!SearchUnit & "*") And
((tblProject.DateReceived) Between Forms![PRSU Project Log]!
SearchStart And Forms![PRSU Project Log]!SearchEnd));

 

[Jeff Boyce]

Thanks for posting the SQL statement.

Now, could you tie together with your original post about inserting some
"IFs"? Is one of them working and the other not? Neither working
independently? Both not working?

"Not working" is a bit generic -- it sounds like the form's fields haven't
been cleared out between queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[christopher.miles.quinn]

Both statements work when entered in separately (however, I cannot
clear the criteria once its selected in the combo box i.e. when
clicking on 'ALL')

Once both the two statements are input into the query (as the SQL
above shows), the criteria are combined in the query results.

 

[Jeff Boyce]

"can't clear the criteria" where? in the form? in the query?

I'm not trying to be difficult, I just don't understand your situation well
enough to offer specific suggestions yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[christopher.miles.quinn]

Sorry im not explaining this very well.

The queries results are displayed in a listbox within a form. When the
criteria are selected in the combo box (again on the form), the combo
box requeries the listbox, creating up to date results.

When the criteria is deselected in the combo box, the results stay the
same in the list box.

 

[Jeff Boyce]

It sounds like the form (?listbox) is not being requeried when the combobox
is emptied.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[christopher.miles.quinn]

What would explain the compiling of criteria when one option in the
combo box is selected after another?

For example, firstly selecting 'Bernard Cook' then selecting 'Paul
Emuss'

 

[christopher.miles.quinn]

Is it possible to enter an IIF statement into the events procedure of
the combo box or list box which would run various queries, depending
on the option selected in the combo box?

 

[Jeff Boyce]

I'm not sure I understand enough to help...

I does seem to me that you could dynamically build your SQL statement,
rather than using the IIF() statements within your WHERE clause. That is,
instead of pointing at the form, you could use what the form's controls
contain to build a WHERE clause.

Yes, you'd probably still use an IF() or a CASE() statement to handle
different paths for what the WHERE clause contains. But no, you wouldn't
have to embed those in the WHERE clause itself.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP