One Result from multiple memo fields

[Sunflower]

I am an access newbie so I am not even sure this is the correct forum
or if 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

 

[Guest]

Try this ---
SELECT tblJob.Job, IIf([Notes] Like "Material*",[Notes],"No Material listing
found") AS [Material]
FROM tblJob LEFT JOIN tblNotes ON
tblJob.ID=tblNotes.JOBID;

 

[Sunflower]

Try this ---
SELECT tblJob.Job, IIf([Notes] Like "Material*",[Notes],"No Material listing
found") AS [Material]
FROM tblJob LEFT JOIN tblNotes ON
tblJob.ID=tblNotes.JOBID;

--
KARL DEWEY
Build a little - Test a little

I am an access newbie so I am not even sure this is the correct forum
or if 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
-----------------

All and any help greatly appreciated- Hide quoted text -

- Show quoted text -

I am new to Access, so pardon my ignorance...

I put the select statement in the report
And I get a syntax error

Here is my SQL statement:
--------------------------------
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
IIf([NOTE] Like "Material*",[NOTE],"No Material listing found") AS
[Material]
FROM dbo_RB_JOBNOTE;