Invalid use of null

[vincent1117]

How do I get around this error with zero field for PrgIV?

SELECT
  TblJohnboyRevenue_Map.CU, dbo_COMPANY.LegalName,
  dbo_STATUS.Descr, dbo_STATUS.Abbr, dbo_COMPANY.City, 
  dbo_COMPANY.State,dbo_COMPANY.CharterNo, dbo_COMPANY.BoardMember, 
  dbo_COMPANY.DATAJack, dbo_REP.DATAJack, dbo_REP.Category, 
  TblJohnboyRevenue_Map.DATE, TblJohnboyRevenue_Map.type, 
  TblJohnboyRevenue_Map.feetype, TblJohnboyRevenue_Map.PROD_DESC,
  IIf([type]="Credit",IIF(dbo_STATUS.Abbr="PRGIV",0,[SumOfPROD_AMT]),[SumOfPROD_AMT]) AS AMOUNT,
  IIf([type]="Credit",IIf([feetype]="SF",[Amount],[Amount]*0.47),IIf([feetype]="SF",[Amount],[Amount]*0.31)) AS Margin
FROM 
  ((TblJohnboyRevenue_Map INNER JOIN dbo_COMPANY ON TblJohnboyRevenue_Map.CU=CStr(dbo_COMPANY.CuNo)) 
  INNER JOIN dbo_STATUS ON dbo_COMPANY.StatusID=dbo_STATUS.StatusID) 
  INNER JOIN dbo_REP ON dbo_COMPANY.DATAJack=dbo_REP.Initials
WHERE 
  dbo_STATUS.Descr<>"PrgIV" And Tbl_Star.DATE=#1/31/2009#;

I've been told to try IF snull or NZ can some provide possible helpful solution or a solution.


Thanks

[Stewart Ross]

Assuming that by 'zero field' you mean null, and assuming by 'PRGIV' you are actually referring to the field dbo_Status.Appr tested in your IIF, use the Nz function to replace the null with an actual value that you can then work on appropriately. If you want the IIF testing field dbo_status.Abbr to return 0 if the field is null or contains the text constant 'PRGIV' you would change the IIF in line 8 to the following:

IIF( Nz(dbo_STATUS.Abbr, "PRGIV") = "PRGIV"), ...

If you want to return a different value for the null case then wrap your existing IIF statement within another IIF as follows:

IIF(dbo_STATUS.Abbr IS NULL, (do something else here), IIF(dbo_Status.Abbr = "PRGIV", ...

Welcome to Bytes!

-Stewart