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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
#4
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|