Hi Duane,
The following is the SQL record source of the query:
SELECT tbl_ProjectDetails.ProjectDetails,
tbl_ProjectDetails.ProjectOfficer,
tbl_ProjectDetails.ProjectStatus,
tbl_ProjectDetails.ProjectManager,
tbl_ProjectDetails.ApprovalDate,
tbl_ProjectDetails.ImplementingAgent,
tbl_ProjectDetails.Address1, tbl_ProjectDetails.Address2,
tbl_ProjectDetails.ProjectDescription,
tbl_ProjectDetails.Address3, tbl_ProjectDetails.Address4,
tbl_ProjectDetails.PostCode, tbl_ProjectDetails.Telephone,
tbl_ProjectDetails.Fax, tbl_ProjectDetails.Email,
tbl_ProjectDetails.Contact, tbl_ProjectDetails.Salutation,
Left([ProjectDetails],3) AS Expr1
FROM tbl_ProjectDetails, tbl_OutcomeGroup
WHERE (((tbl_ProjectDetails.ProjectDetails) Is Not Null)
AND ((Left([ProjectDetails],3))=
Code:
))
ORDER BY tbl_ProjectDetails.ProjectDetails;
[QUOTE]
-----Original Message-----
Ammo replied:
"Still not working, the query produces blank results. When I add the
expression as a new column in the query, it automatically becomes:
Expr1: Left([ProjectDetails],3)
Do I need to do something with the relationships between the tables?"
Duane's reply:
You don't need to set relationships to the new table in the report's
recordsource. Add the criteria of [Code] under the column Expr1:
Left([ProjectDetails],3) should match each record in your report with one
record from tbl_OutcomeGroup. If you can't get this to work, then reply back
with the SQL view of your report's record source.
--
Duane Hookom
MS Access MVP
--
[QUOTE]
Good job on creating a table. That was the start.
You then should have created a new, calculated column[/QUOTE] in the query with
the[QUOTE]
expression:
Left(ProjectDetails,3)
and then set its criteria to
[Code]
--
Duane Hookom
MS Access MVP
--
Hi Duane,
Thanks for your reply, but I am still having trouble with
implementing this problem. I created a table like you
recommended with the corresponding data. I then carried
out the following:
1)Opened the report in design view (the report is designed
from qry_ProjectDetails)
2)Selected report properties.
3)Clicked the 3 dots next to the ‘Record Source’ item.
4)Added the table that you told me to create (named this
tbl_OutcomeGroup)
5)I then added the fields from tbl_OutcomeGroup to the
qry_ProjectDetails.
After completing the above steps, I didn’t know what next
steps to take. I tried adding the expression, WHERE Left
(ProjectDetails,3) = [Code] under the ‘Sequence’ field in
the ‘Criteria’ section of the query, qry_ProjectDetail,
but kept receiving a message illegal operand, do you know
what I am doing wrong, if so please can you help?
Cheers
Ammo
-----Original Message-----
If I understand correctly I would create a tables
Code Description Sequence
AS1 Arts 1
AS2 Sports 2
CS1 Communications 3
CS2 Young Peoples 4
You can then add this table to your report's record
source and set a
criteria like
WHERE Left(ProjectDetails,3) = [Code]
Add the fields to the grid so you can group and sort on
the Sequence field.
--
Duane Hookom
MS Access MVP
I have created a report with the following headings that
contains the following sample data:
ProjectDetails ProjectOfficer ImplementingAgent
AS1/060 Arts proj Officer1 Company1
AS2/003 Sports proj Officer2 Company2
CS1/079 Comms proj Officer3 Company3
CS2/075 Young proj Officer4 Company4
On the report I wish it to be displayed as follows:
ProjectDetails ProjectOfficer ImplementingAgent
Arts Outcome
AS1/060 Arts proj Officer1 Company1
Sports Outcome
AS2/003 Sports proj Officer2 Company2
Communications Outcome
CS1/079 Comms proj Officer3 Company3
Young Peoples Outcome
CS2/075 Young Officer4 Company4
Any records with AS1 will come under a Arts sub heading
Any records with AS2 will come under Communications sub
heading.
etc
The report criteria will be set by the code (AS2, CS1
etc)
in the ProjectDetails field. Anyone one have any ideas
how
this can be done?
Regards
Ammo
.
[/QUOTE]
.
[/QUOTE][/QUOTE]
