A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Invalid use of Null



 
 
Thread Tools Display Modes
  #1  
Old June 29th, 2009, 05:22 PM posted to microsoft.public.access.tablesdbdesign
New Beginner
external usenet poster
 
Posts: 1
Default Invalid use of Null

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.




  #2  
Old June 29th, 2009, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default Invalid use of Null

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


"New Beginner" wrote:

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.




  #3  
Old June 29th, 2009, 05:55 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Invalid use of Null

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" New wrote in message
...
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.






  #4  
Old June 29th, 2009, 06:26 PM posted to microsoft.public.access.tablesdbdesign
New Beginner[_2_]
external usenet poster
 
Posts: 1
Default Invalid use of Null

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" wrote:

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" New wrote in message
...
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.







  #5  
Old June 29th, 2009, 08:05 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Invalid use of Null

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" wrote in message
...
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" wrote:

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" New wrote in message
...
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.









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.