Invalid Use of Null Error

[AC5FF]

This one is driving me nuts.

I have this query that occasionally will give me the error "Invalid Use of Null".
Luckly though - or so I think - I have a copy of this query. When I get the error, I delete the offending query and then copy the copy of it and re-name it the original name. That's ALL I do and things start working again. A few hours or a few days later I'll start getting the same error; and I fix it the same way.

Does anyone see any problem in this SQL that could possibly cause this?

SELECT lru.Team, Status.[Code Order], Status.Code, Work.Doc, IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)) AS Awp, IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)) AS Dwp, Work.ISU, IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc)," ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr AS ABBR, IIf([Work]![Shop Unit]=False,([lru]![Maint Std]),0) AS [Maint Std], lru.[cnd rate], Work.[S/N], Work.Maint, IIf([Work]![Shop Unit]=False,IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),[Work]![Shop Comment]) AS Priority, CStr(IIf(Work!Repeat>0,Work!Repeat,"")) AS Repeat, CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)) AS Sort1, IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0) AS Sort2, CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])) AS Sort3, IIf(lru!DIG,"{","") & Area!Name & IIf(lru!PCAT,"/PCAT","") & IIf(lru!DIG,"}","") AS Name, lru.DIG, Area.Element, Work.BACT, lru.enterprise
FROM ((d23 INNER JOIN (Status INNER JOIN ((Area INNER JOIN lru ON Area.ID = lru.Team) INNER JOIN ([Work] LEFT JOIN MD5TeamProductionQTYAWFAWP ON (Work.Status = MD5TeamProductionQTYAWFAWP.Status) AND (Work.[LRU ID] = MD5TeamProductionQTYAWFAWP.ID)) ON lru.ID = Work.[LRU ID]) ON Status.ID = Work.Status) ON d23.DOC = Work.Doc) INNER JOIN MP ON d23.MP = MP.MP) LEFT JOIN [Enterprise Repair Status] ON lru.Abbr = [Enterprise Repair Status].LRU
WHERE (((Work.Status)<4))
GROUP BY lru.Team, Status.[Code Order], Status.Code, Work.Doc, IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)), IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)), Work.ISU, IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc)," ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr, IIf([Work]![Shop Unit]=False,([lru]![Maint Std]),0), lru.[cnd rate], Work.[S/N], Work.Maint, IIf([Work]![Shop Unit]=False,IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),[Work]![Shop Comment]), CStr(IIf(Work!Repeat>0,Work!Repeat,"")), CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)), IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0), CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])), IIf(lru!DIG,"{","") & Area!Name & IIf(lru!PCAT,"/PCAT","") & IIf(lru!DIG,"}",""), lru.DIG, Area.Element, Work.BACT, lru.enterprise
ORDER BY IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0) DESC , CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])) DESC;

Thx

 

[boblarson]

Perhaps if we format it so it is more readible here:

SELECT lru.Team, Status.[Code Order], Status.Code, Work.Doc, 
IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)) AS Awp, 
IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)) AS Dwp, Work.ISU, 
IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc), " ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr AS ABBR, 
IIf([Work]![Shop Unit]=False,([lru]![Maint Std]),0) AS [Maint Std], 
lru.[cnd rate], Work.[S/N], Work.Maint, 
IIf([Work]![Shop Unit]=False,
IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),
[Work]![Shop Comment]) AS Priority, 
CStr(IIf(Work!Repeat>0,Work!Repeat,"")) AS Repeat, 
CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)) AS Sort1, 
IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0) AS Sort2, 
CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])) AS Sort3, IIf(lru!DIG,"{","") & Area!Name & IIf(lru!PCAT,"/PCAT","") & IIf(lru!DIG,"}","") AS Name, 
lru.DIG, Area.Element, Work.BACT, lru.enterprise
FROM ((d23 INNER JOIN (Status INNER JOIN ((Area INNER JOIN lru ON Area.ID = lru.Team) 
INNER JOIN ([Work] LEFT JOIN MD5TeamProductionQTYAWFAWP ON (Work.Status = MD5TeamProductionQTYAWFAWP.Status) 
AND (Work.[LRU ID] = MD5TeamProductionQTYAWFAWP.ID)) ON lru.ID = Work.[LRU ID]) ON Status.ID = Work.Status) ON d23.DOC = Work.Doc) 
INNER JOIN MP ON d23.MP = MP.MP) LEFT JOIN [Enterprise Repair Status] ON lru.Abbr = [Enterprise Repair Status].LRU
WHERE (((Work.Status)<4))
GROUP BY lru.Team, Status.[Code Order], Status.Code, Work.Doc, IIf(Work!Status<>2,Work!Awp,WPDays(Work![AWP Start],Work!Awp)), 
IIf(Work!Status<>3,Work!Dwp,WPDays(Work![DWP Start],Work!Dwp)), Work.ISU, IIf(IsNull(MD5TeamProductionQTYAWFAWP!CountOfDoc), " ","x" & MD5TeamProductionQTYAWFAWP!CountOfDoc & " ") & lru!Abbr, 
IIf([Work]![Shop Unit]=False,([lru]![Maint Std]),0), lru.[cnd rate], Work.[S/N], Work.Maint, 
IIf([Work]![Shop Unit]=False,IIf([Work]![MICAP]=True,"MICAP",[D23]![MP]),[Work]![Shop Comment]), 
CStr(IIf(Work!Repeat>0,Work!Repeat,"")), 
CStr(IIf(Work![Shop Unit]=False,IIf(Work!MICAP=True,2,1),0)), 
IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0), 
CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])), 
IIf(lru!DIG,"{","") & Area!Name & IIf(lru!PCAT,"/PCAT","") & 
IIf(lru!DIG,"}",""), lru.DIG, Area.Element, 
Work.BACT, lru.enterprise
ORDER BY IIf([Work]![shop unit]=False And ([enterprise repair status]![contracted]-[enterprise repair status]![repaired])>0,1,0) DESC , 
CStr(IIf([Work]![Status]<2,[MP]![Priority],[lru]![Abbr])) DESC;

 

[AC5FF]

Bob;
without going line by line - is this just a repost of what I put in?

 

[boblarson]

Bob;
without going line by line - is this just a repost of what I put in?

Yes, it was to try to help someone actually read it because your initial post was extremely difficult to read.

 

[AC5FF]

gotcha
I used 'quote' instead of 'code' because I thought it would be easier to read! lol I'll get it straight eventually