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.