Invalid use of Null

[vikram_singh]

Hi,

M getting error (Invalid use of Null) with crosstab where i have used
left outer join. I tried different ways to get rid of error message
but it follows all the time. I tried using "Nz" "IIF" but didnt
work. Please help if someone can help me on my below query.

TRANSFORM Sum(IIf([ANR]>0,[ANR],0)) AS Expr1
SELECT report4.country
FROM report4 LEFT JOIN REPORT4_ACTUALS ON (report4.PL =
REPORT4_ACTUALS.PL)
GROUP BY report4.country
PIVOT REPORT4_ACTUALS.Expr2


Thanks in advance,
Vikram

 

[Jerry Whittle]

Hi,

Try first creating a normal select query that returns the records which you
want, including the ANR manipulation, and saving it. Use this saved query as
the basis for your crosstab.

 

[Klatuu]

The only way to check for Null is with the IsNull statment; however, you can
also use the Nz function in this case to avoid the error. You don't need it
IIf statement for this:

TRANSFORM Sum(Nz([ANR],0)) AS Expr1

--
Dave Hargis, Microsoft Access MVP

Hi,

M getting error (Invalid use of Null) with crosstab where i have used
left outer join. I tried different ways to get rid of error message
but it follows all the time. I tried using "Nz" "IIF" but didnt
work. Please help if someone can help me on my below query.

TRANSFORM Sum(IIf([ANR]>0,[ANR],0)) AS Expr1
SELECT report4.country
FROM report4 LEFT JOIN REPORT4_ACTUALS ON (report4.PL =
REPORT4_ACTUALS.PL)
GROUP BY report4.country
PIVOT REPORT4_ACTUALS.Expr2


Thanks in advance,
Vikram

 

[Michel Walsh]

Do you get an invalid use of null when you try to capture some data into a
VBA variable (or when passing some data into a VBA procedure) or when you
JUST run the query in the User Interface? 'cause if it involves VBA, you
may be using a basic data type, instead of a VARIANT, since ONLY a VARIANT,
as 'basic' value-datatype, can handle a null:

Dim i AS integer
i=null


is an error. Also:




Sub myFunction( i As integer, ... other args... )
...
End Sub

....

myFunction null , ....


is also an error, since, for a moment, an integer will try to hold a NULL,
and it can't.




myFunction recordset.Fields("fieldName"), ...


would also produce a runtime error, if the field returns a NULL. Same reason
as before.




Hoping it may help,
Vanderghast, Access MVP

 

[vikram_singh]

Do you get an invalid use of null when you try to capture some data into a
VBA variable (or when passing some data into a VBA procedure) or when you
JUST run the query in the User Interface? 'cause if it involves VBA, you
may be using a basic data type, instead of a VARIANT, since ONLY a VARIANT,
as 'basic' value-datatype, can handle a null:

Dim i AS integer
i=null

is an error. Also:

Sub myFunction( i As integer, ... other args... )
...
End Sub

....

myFunction null , ....

is also an error, since, for a moment, an integer will try to hold a NULL,
and it can't.

myFunction recordset.Fields("fieldName"), ...

would also produce a runtime error, if the field returns a NULL. Same reason
as before.

Hoping it may help,
Vanderghast, Access MVP

Hi,

M getting error (Invalid use of Null) with crosstab where i have used
left outer join. I tried different ways to get rid of error message
but it follows all the time. I tried using "Nz" "IIF" but didnt
work. Please help if someone can help me on my below query.

TRANSFORM Sum(IIf([ANR]>0,[ANR],0)) AS Expr1
SELECT report4.country
FROM report4 LEFT JOIN REPORT4_ACTUALS ON (report4.PL =
REPORT4_ACTUALS.PL)
GROUP BY report4.country
PIVOT REPORT4_ACTUALS.Expr2

Thanks in advance,
Vikram- Hide quoted text -

- Show quoted text -

Hi,

Thanks for you reply. Actually i have modified the query and i am
writing it below but the problem is still with it.

SELECT report4.country, REPORT4_ACTUALS.ANR, REPORT4_ACTUALS.EXPR2
FROM report4 LEFT JOIN REPORT4_ACTUALS ON (report4.PL =
REPORT4_ACTUALS.PL)


if i execute the above query it works fine except showing one error
message #Error in REPORT4_ACTUALS.EXPR2 Column. To handle the #Error
if I also modified the query saying
IIF(ISERROR(REPORT4_ACTUALS.EXPR2)=TRUE,"",
REPORT4_ACTUALS.EXPRT2) ...BUT SOCKINGLY IS NOT WORKING. it still
shows #Error.... And when i add one more line say "Group By " then i
get error Invalid use of Null". I am not using any VBA code..and it
is simple query....