left join with where problem

[anglophobe]

I am trying to join several tables through inner and left joins.
[tt]
Problem:
Prob# Type Date
01 KE Jan 01
02 P Jan 05

Incident
Inc# Date Plink
01 Jan 03 01
02 Jan 07 01
03 Jan 11 02

Audit
Parent_Link FN
01 TypeOfProblem
01 Status
02 Status

What I need to end up with is:

Prob# Type PDate FN Inc# IDate
01 KE Jan 01 Type 01 Jan 03
01 KE Jan 01 Type 02 Jan 07
02 P Jan 05 03 Jan 11
[/tt]
So far I can do the problem and incident parts, but am unable to get the Audit/FieldName part working

My code I was attempting is this: (you can ignore the FusionLink table, that bit works fine)

SELECT Prob.ProblemNumber, Inc.IncidentNumber, Prob.TypeOfProblem, Prob.CreatedDateTime, Inc.CreatedDateTime, Aud.FieldName

FROM (((dbo_Problem Prob

INNER JOIN dbo_FusionLink Fus ON Prob.RecId=Fus.TargetID) 

INNER JOIN dbo_Incident Inc ON Inc.RecId=Fus.SourceID)

LEFT JOIN dbo_AuditHistory Aud ON Aud.ParentLink_RecID = Prob.RecID WHERE Aud.FieldName = "TypeofProblem")

ORDER BY Prob.ProblemNumber

But it gives me an error. Taking out the WHERE expression works. Any suggestions?
I’m on CR10, and do not have access to the db, just a generic connection.

 

[Madawc]

As a work-round, leave out the WHERE and group the report by Aud.FieldName. Use Report > Selection Formuals > Group to suppress other groups. Maybe do the test on a formula field,

if isnull(Aud.FieldName) then "None"
else if Aud.FieldName = "TypeofProblem" then "Found"
else "Other".

Group on this and then suppress "Other".

You'd have a problem anyway trying to do a WHERE test on a field linked by a Left Outer. One is supposed to be able to get round this by first testing the value for null, but I've never been able to get this to work.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10