By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,078 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,078 IT Pros & Developers. It's quick & easy.

Invalid use of null

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

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   TblJohnboyRevenue_Map.CU, dbo_COMPANY.LegalName,
  3.   dbo_STATUS.Descr, dbo_STATUS.Abbr, dbo_COMPANY.City, 
  4.   dbo_COMPANY.State,dbo_COMPANY.CharterNo, dbo_COMPANY.BoardMember, 
  5.   dbo_COMPANY.DATAJack, dbo_REP.DATAJack, dbo_REP.Category, 
  6.   TblJohnboyRevenue_Map.DATE, TblJohnboyRevenue_Map.type, 
  7.   TblJohnboyRevenue_Map.feetype, TblJohnboyRevenue_Map.PROD_DESC,
  8.   IIf([type]="Credit",IIF(dbo_STATUS.Abbr="PRGIV",0,[SumOfPROD_AMT]),[SumOfPROD_AMT]) AS AMOUNT,
  9.   IIf([type]="Credit",IIf([feetype]="SF",[Amount],[Amount]*0.47),IIf([feetype]="SF",[Amount],[Amount]*0.31)) AS Margin
  10. FROM 
  11.   ((TblJohnboyRevenue_Map INNER JOIN dbo_COMPANY ON TblJohnboyRevenue_Map.CU=CStr(dbo_COMPANY.CuNo)) 
  12.   INNER JOIN dbo_STATUS ON dbo_COMPANY.StatusID=dbo_STATUS.StatusID) 
  13.   INNER JOIN dbo_REP ON dbo_COMPANY.DATAJack=dbo_REP.Initials
  14. WHERE 
  15.   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
Jun 26 '09 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. IIF(dbo_STATUS.Abbr IS NULL, (do something else here), IIF(dbo_Status.Abbr = "PRGIV", ... 
Welcome to Bytes!

-Stewart
Jun 29 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.