Frustrated by "Invalid use of Null"

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
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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
 
Top