Invalid use of Null

  • Thread starter Thread starter New Beginner
  • Start date Start date
N

New Beginner

Can anyone help me figure this out?

This statement works:

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State, dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRGIV" And TblEricRevenue_Star.DATE=#1/31/2009#;



When I changed the "PRG" I get this error:

Invalid use of Null

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State, dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRG" And TblEricRevenue_Star.DATE=#1/31/2009#;

I just want it to show all the descriptions. I don’t want it to exclude the
PRGIV desc. As its doing right now.
 
Some column you are using contains a null value. you need to look at the data
in your tables. It's probalbly a column you are summing or doing a
calculation on (maybe amount). Columns that contain numeric information
should have a default of 0 (zero) not null.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Dorian & I have somewhat different opinions about using a value of "0"
(zero) for numeric fields.

The number "0" (zero) indicates "none of it", while a Null indicates
"unknown". As an example, the answer to "How many people were there?" could
be "0" (none), or could be "I don't know" (null).

As an alternative, consider the use of the Nz() function. This allows you
to have a "null" in your field, but can convert it to whatever value is
appropriate if you need to "do math" on that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
When I us the NZ Function I’m getting the error:

Wrong number of arguments used with function in query expression

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State, dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.PROD_DESC,

IIf(Nz([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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRGIV" And TblEricRevenue_Star.DATE=#1/31/2009#;










Jeff Boyce said:
Dorian & I have somewhat different opinions about using a value of "0"
(zero) for numeric fields.

The number "0" (zero) indicates "none of it", while a Null indicates
"unknown". As an example, the answer to "How many people were there?" could
be "0" (none), or could be "I don't know" (null).

As an alternative, consider the use of the Nz() function. This allows you
to have a "null" in your field, but can convert it to whatever value is
appropriate if you need to "do math" on that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

New Beginner said:
Can anyone help me figure this out?

This statement works:

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State,
dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRGIV" And TblEricRevenue_Star.DATE=#1/31/2009#;



When I changed the "PRG" I get this error:

Invalid use of Null

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State,
dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRG" And TblEricRevenue_Star.DATE=#1/31/2009#;

I just want it to show all the descriptions. I don't want it to exclude
the
PRGIV desc. As its doing right now.
Click to expand...
Click to expand...
 
The error message suggests that your syntax doesn't match what the Nz()
function expects... or perhaps the IIF() function...

Check Access HELP for the correct syntax and examples.

Regards

Jeff Boyce
Microsoft Office/Access MVP

New Beginner said:
When I us the NZ Function I'm getting the error:

Wrong number of arguments used with function in query expression

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State,
dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.PROD_DESC,

IIf(Nz([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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRGIV" And TblEricRevenue_Star.DATE=#1/31/2009#;










Jeff Boyce said:
Dorian & I have somewhat different opinions about using a value of "0"
(zero) for numeric fields.

The number "0" (zero) indicates "none of it", while a Null indicates
"unknown". As an example, the answer to "How many people were there?"
could
be "0" (none), or could be "I don't know" (null).

As an alternative, consider the use of the Nz() function. This allows
you
to have a "null" in your field, but can convert it to whatever value is
appropriate if you need to "do math" on that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

New Beginner said:
Can anyone help me figure this out?

This statement works:

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State,
dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRGIV" And
TblEricRevenue_Star.DATE=#1/31/2009#;



When I changed the "PRG" I get this error:

Invalid use of Null

SELECT TblEricRevenue_Star.CU, dbo_COMPANY.LegalName, dbo_STATUS.Descr,
dbo_STATUS.Abbr, dbo_COMPANY.City, dbo_COMPANY.State,
dbo_COMPANY.CharterNo,
dbo_COMPANY.BoardMember, dbo_COMPANY.TNBRep, dbo_REP.TNBRep,
dbo_REP.Category, TblEricRevenue_Star.DATE, TblEricRevenue_Star.type,
TblEricRevenue_Star.feetype, TblEricRevenue_Star.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 ((TblEricRevenue_Star INNER JOIN dbo_COMPANY ON
TblEricRevenue_Star.CU=CStr(dbo_COMPANY.CuNo)) INNER JOIN dbo_STATUS ON
dbo_COMPANY.StatusID=dbo_STATUS.StatusID) INNER JOIN dbo_REP ON
dbo_COMPANY.TNBRep=dbo_REP.Initials

WHERE dbo_STATUS.abbr<>"PRG" And TblEricRevenue_Star.DATE=#1/31/2009#;

I just want it to show all the descriptions. I don't want it to
exclude
the
PRGIV desc. As its doing right now.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top