query join with criteria fails, join not supported

I would move this
AND tblpulls.pulledstatus <> "O"
down into the Where clause

except that simply moving that criteria down into the WHERE clause will negate the LEFT JOIN.  You have to account for the NULLS (no match) in that field [PulledStatus].  You can do that with syntax like:

AND ( (tblpulls.pulledStatus IS NULL) OR (tblpulls.pulledstatus <> "O"))

but I prefer a syntax similar to:

AND NZ(tblpulls.pulledstatus, "") <> "O"


You might try:

SELECT DISTINCT tblFixedAssets.FixedAssetID
      ,tblFixedAssets.fldBarCode
      ,tblFixedAssets.Staff_ID
      ,tblFixedAssets.Model
      ,tblFixedAssets.fldAssetStatus
      ,Inventory.[Item Number]
      ,Inventory.fldNonBarCode
      ,tblPulls.PulledStatus
FROM (
      tblFixedAssets INNER JOIN Inventory ON tblFixedAssets.Staff_ID = Inventory.[Item Number]
      )
LEFT JOIN tblPulls ON tblFixedAssets.fldBarCode = tblPulls.ItemBarCode
WHERE tblFixedAssets.Staff_ID = 'Lens1.54-1.93-Evision'
      AND tblFixedAssets.fldAssetStatus = 'Active'
      AND NZ(tblpulls.pulledstatus, "") <> "O"

You need to alias your subquery but honestly it doesn't appear as if you really need it set up as one anyway

SELECT DISTINCT A.FixedAssetID
	,A.fldBarCode
	,A.Staff_ID
	,A.Model
	,A.fldAssetStatus
	,I.[Item Number]
	,I.fldNonBarCode
	,P.PulledStatus
FROM tblFixedAssets AS A
INNER JOIN Inventory AS I
   ON tblFixedAssets.Staff_ID = Inventory.[Item Number]
LEFT JOIN tblPulls AS P
   ON A.fldBarCode = P.ItemBarCode
   AND P.pulledstatus <> "O"
WHERE A.Staff_ID = 'Lens1.54-1.93-Evision'
	AND A.fldAssetStatus = 'Active'

Select allOpen in new window

pressMac, do you still need help with this question?

Sorry It took so long, but that makes it work.