DLookUp in Query Issues

[padlocked17]

Can anyone decipher what I'm doing wrong with the following syntax:

SELECT tblEnrollment.EnrollmentID, tblEvalTypes.Abbreviation, tblOptionsBF.Abbreviation, tblEvalResults.EvalDate, tblEvalResults.FlightExaminer, DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]='" & [tblEvalResults].[FlightExaminer] & "'") AS FE, tblGrades.Abbreviation, tblEvalResults.EPE
FROM tblEvalTypes INNER JOIN (tblGrades INNER JOIN (tblOptionsBF RIGHT JOIN ((tblMembers INNER JOIN tblEnrollment ON tblMembers.MemberID = tblEnrollment.MemberID) INNER JOIN tblEvalResults ON tblEnrollment.EnrollmentID = tblEvalResults.EnrollmentID) ON tblOptionsBF.OptionsBFID = tblEvalResults.BoldfaceID) ON tblGrades.GradesID = tblEvalResults.GradeID) ON tblEvalTypes.EvalTypesID = tblEvalResults.EvalTypesID
WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID]));

The problem child is:

DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]='" & [tblEvalResults].[FlightExaminer] & "'") AS FE

Thanks!

 

[KeithG]

Looks correct to me. Why not join tblMembers.MemberID to [tblEvalResults].[FlightExaminer]?

 

[padlocked17]

That's what I thought as well. But when I view the results of thequery I get an #Error in that field and the error:

"Data type mismatch in criteria expression"

I'm really lost on this one.

I can't join those two fields because I get the error : "The SQL statement could not be excuted because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement"

 

[daved]

The SQL error re ambiguus outer joins is caused by the 'RIGHT JOIN ((tblMembers' - it must be an INNER JOIN because otherwise you're asking the query to return all members of the right hand table.

The type mismatch is because (presumably) MemberID is numeric and you've typed it as string in the dlookup statement. Take out the single quotes to give:
DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]=tblEvalResults].[FlightExaminer]") AS FE

g'luck

 

[daved]

Incidentally, I'd wrap the dlookup in a Nz function - Nz(dlookup(....)) or Nz(dlookup(....),0) just in case MemberID is Null. Also, you'll probably need to move the quotes so they don't include MemberID, thus:
Nz(DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]=" & tblEvalResults].[FlightExaminer]),0) AS FE
dave

 

[Pat Hartman]

Never use DLookup() in a query. Change your query so that it joins to the lookup table. That will be much more efficient.