Frustrated by "Invalid use of Null"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This query worked fine before the holidays but now I'm getting the "Invalid
use of Null" error. The only thing that has changed is that a few more days
worth of sales records have been added to the ['05 Sales] table. Here is the
code -

SELECT ['05 Sales].ID, ['05 Sales].FisMon, ['05 Sales].Division, ['05
Sales].CustNo, ['05 Sales].CustomerName, ['05 Sales].ItemDescription, ['05
Sales].[$Sale(AUD)], qryExTestSales.EFlag, S_Ex_Test.SemiEx_ct,
Imp_Ex_Test.ImpEx_ct, ['05 Sales].ShipFrWh,
IIf(Nz([EFlag])="Y","Exact",IIf(Nz([SemiEx_ct])="Y","Semi-Exact",IIf(Nz([ImpEx_ct])="Y","Imp-Exact",IIf(Nz(['05
Sales].[CustNo])=933905,"Hally","Non-Exact")))) AS Ex_Cat, ['05
Sales].ProdGrp, ['05 Sales].M2Shipped, ['05 Sales].[Order Class], IIf(Nz(['05
Sales]![Facility])="MC","AD",IIf(Nz(['05 Sales]![Facility])="MT","ME",['05
Sales]![Facility])) AS Rev_Fac, ['05 Sales].ProdCls, ['05 Sales].OrderDate,
['05 Sales].[ShipDte/PickConf], qryQtrMnthTest.YMonth, qryQtrMnthTest.YQtr
FROM (((['05 Sales] LEFT JOIN Imp_Ex_Test ON ['05 Sales].ID =
Imp_Ex_Test.ID) LEFT JOIN S_Ex_Test ON ['05 Sales].ID = S_Ex_Test.ID) LEFT
JOIN qryExTestSales ON ['05 Sales].ID = qryExTestSales.ID) INNER JOIN
qryQtrMnthTest ON ['05 Sales].ID = qryQtrMnthTest.ID;

I have only just thrown in the NZ() functions in the vain hope that they may
help but to no avail.

Could someone please tell me where I should be looking for the problem.

Thanks,

IK
 
When you use the NZ function, you need to specify which value you want
instead of the Null

Nz(FieldName,0)
Will replace null with 0

But
Nz(FieldName)
Will still return the field value, even if it Null
 
Thanks Ofer.

I actually tracked down the Null in a field I did not expect but your
reminder of the proper use of the Nz function helped get the problem solved.

Cheers,

IK

Ofer said:
When you use the NZ function, you need to specify which value you want
instead of the Null

Nz(FieldName,0)
Will replace null with 0

But
Nz(FieldName)
Will still return the field value, even if it Null

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



SthOzNewbie said:
This query worked fine before the holidays but now I'm getting the "Invalid
use of Null" error. The only thing that has changed is that a few more days
worth of sales records have been added to the ['05 Sales] table. Here is the
code -

SELECT ['05 Sales].ID, ['05 Sales].FisMon, ['05 Sales].Division, ['05
Sales].CustNo, ['05 Sales].CustomerName, ['05 Sales].ItemDescription, ['05
Sales].[$Sale(AUD)], qryExTestSales.EFlag, S_Ex_Test.SemiEx_ct,
Imp_Ex_Test.ImpEx_ct, ['05 Sales].ShipFrWh,
IIf(Nz([EFlag])="Y","Exact",IIf(Nz([SemiEx_ct])="Y","Semi-Exact",IIf(Nz([ImpEx_ct])="Y","Imp-Exact",IIf(Nz(['05
Sales].[CustNo])=933905,"Hally","Non-Exact")))) AS Ex_Cat, ['05
Sales].ProdGrp, ['05 Sales].M2Shipped, ['05 Sales].[Order Class], IIf(Nz(['05
Sales]![Facility])="MC","AD",IIf(Nz(['05 Sales]![Facility])="MT","ME",['05
Sales]![Facility])) AS Rev_Fac, ['05 Sales].ProdCls, ['05 Sales].OrderDate,
['05 Sales].[ShipDte/PickConf], qryQtrMnthTest.YMonth, qryQtrMnthTest.YQtr
FROM (((['05 Sales] LEFT JOIN Imp_Ex_Test ON ['05 Sales].ID =
Imp_Ex_Test.ID) LEFT JOIN S_Ex_Test ON ['05 Sales].ID = S_Ex_Test.ID) LEFT
JOIN qryExTestSales ON ['05 Sales].ID = qryExTestSales.ID) INNER JOIN
qryQtrMnthTest ON ['05 Sales].ID = qryQtrMnthTest.ID;

I have only just thrown in the NZ() functions in the vain hope that they may
help but to no avail.

Could someone please tell me where I should be looking for the problem.

Thanks,

IK
Click to expand...
Click to expand...
 
Thanks Allen.

I checked this link out earlier when I was still convinced I wasn't dealing
with any Null values. Some of the tips made me look a little harder.

Cheers,

IK
 
Back
Top