One result from multiple memo fields

[Sunflower]

If I have duplicated this post I am sorry,
my computer froze, so I wasn't sure if the first post got thru.

I am an access newbie so I am not even sure this can be done...

I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...

My report has the following record source:
SELECT tblJob.Job, tblNotes.Notes FROM tblJob LEFT JOIN tblNotes ON
tblJob.ID=tblNotes.JOBID;

The report has a Group header on Job
and a [Material] text field with the following control source:
=IIf([Notes] Like "Material*",[Notes],"No Material listing found")

What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
-----------------

I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found

 

[John Spencer]

You might try modifying your query. Note that the following can only be
done in the SQL view and cannot be done in the query (grid) view.

SELECT tblJob.Job, T.Notes
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;

Then you could change the report control to show No Material Listing found
either by using the format property of the control bound to Notes
Format: @;"No Material Listing Found"

Or by setting the control's source to
=NZ([Notes],"No Material Listing Found")

OR you could further modify the above query to

SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..