Try to simplify your Where clause:
WHERE tblCounterparty.Name Not Like "Markus*" AND
tblCounterpartyDocument.[Signed Date] Is Null AND
tblDocumentStatus.[Completed Date] Is Null AND
(tblCounterparty.Priority=[Forms]![frmPriority]![Combo4] OR
Nz([Forms]![frmPriority]![Combo4], "All")="All")
I've added the Nz in there to handle the case where they haven't selected
anything from the combo box. (I've defaulted nothing selected to mean the
same as select all. You can change that if you need to...)
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message The report is just basic it returns all fields of a query which is
simply
just a selection of fields from 7 tables, so the SQL is quite large.
Here
it
is....
SELECT tblCounterparty.Name, tblDocument.[Document Name],
tblCounterpartyDocument.[Signed Date], tblType.[Type Name],
tblDocumentStatus.[Action Date], tblDocumentStatus.[Start Date],
tblDocumentStatus.Comment, tblCounterparty.[Commericial Contact],
tblDocsNegotiator.[Docs Negotiator Name], tblCreditOfficer.[Credit
Officer
Name], tblCounterparty.Priority, tblDocumentStatus.[Completed Date]
FROM tblDocsNegotiator RIGHT JOIN ((tblDocument RIGHT JOIN
(tblCreditOfficer
RIGHT JOIN (tblCounterparty INNER JOIN tblCounterpartyDocument ON
tblCounterparty.[CounterParty ID] =
tblCounterpartyDocument.[CounterParty
ID]) ON tblCreditOfficer.[Credit Officer ID] = tblCounterparty.[Credit
Officer ID]) ON tblDocument.[Document ID] =
tblCounterpartyDocument.[Document
ID]) LEFT JOIN (tblType RIGHT JOIN tblDocumentStatus ON tblType.[Type
ID]
=
tblDocumentStatus.[Type ID]) ON (tblCounterpartyDocument.[Document ID]
=
tblDocumentStatus.[Document ID]) AND
(tblCounterpartyDocument.[CounterParty
ID] = tblDocumentStatus.[CounterParty ID])) ON tblDocsNegotiator.[Docs
Negotiator ID] = tblCounterparty.[Docs Negotiator ID]
WHERE (((tblCounterparty.Name) Not Like "Markus*") AND
((tblCounterpartyDocument.[Signed Date]) Is Null) AND
((tblCounterparty.Priority)=[Forms]![frmPriority]![Combo4]) AND
((tblDocumentStatus.[Completed Date]) Is Null)) OR
(((tblCounterparty.Name)
Not Like "Markus*") AND ((tblCounterpartyDocument.[Signed Date]) Is
Null)
AND
((tblDocumentStatus.[Completed Date]) Is Null) AND
(([Forms]![frmPriority]![Combo4])="All"))
ORDER BY tblCounterparty.Name, tblCounterparty.Priority;
Thanks,
B/
:
What's the SQL for the query that the report's based on?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message Hi Douglas,
Happy New Year.
I have got the query to work. From the form I enter the priority
number
and
have a button to open the report, and it correctly displays all
records
with
that priority.
From your suggestions, I have used a value list 1-5 for priorities
and
the
piece of VBA code from Access Web to display (All) as an option.
Private Sub Form_Open(Cancel As Integer)
With Me.Combo4
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With
End Sub
However when attempting to display all records from the form, I get
an
error
message stating that "This expression is typed incorrectly, or is
too
complex
to be evaluated. For example a numeric expression may contain too
many
complicated elements.... "
Am I missing something to display all records or did you expect it
to
work
from what I have done so far?
Thanks again,
B/
:
And just in case I've misinterpretted "still having trouble
bringing
up
the
combo box", hopefully you noticed my comment to "Make sure that the
form
is
open when the query runs."
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
in
message Hi Douglas,
Thanks for this.
I have added All to the query but I'm still having trouble
bringing
up
the
combo box.
Do you want me to use add the first line in criteria and the
second
line
in
or?
so...
Criteria: < [Forms]![frmPriority]![Combo4]
or: ([Forms]![frmPriority]![Combo4] = "All")
Like this ?
B/
:
You cannot make Access prompt you with a combo box. However, you
can
create
an unbound form with a combo box that has four values: 1, 2, 3
and
All
(if
you're getting the numbers from a table rather than hard-coding
them,
see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web"
for
how
to
add "All" to your query)
Change your query's criteria to
< [Forms]![NameOfForm]![NameOfComboBox) OR
([Forms]![NameOfForm]![NameOfComboBox) = "All")
Make sure that the form is open when the query runs: Access will
not
open
the form for you either.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Bhupinder Rayat" <
[email protected]>
wrote
in
message
Hi,
I have a report which opens a query, based on the priority
number
(1,2
or
3).
In the query criterira for Priority I have the following.
<[Show all CP with priority <=]
This brings up a prompt box when I open the report where I
type
1, 2
or
3
to
bring up the records with that priority.
I want to change the caption to a combo box where I can select
1,
2,
or
3
rather than typing it in, and also have the option to display
ALL
records.
