If field is zero show 0

aldeb

Registered User.
Local time
Today, 13:50
Joined
Dec 23, 2004
Messages
318
Below is the SQL I have on a query. This query is made up of 4 other queries. If any of the fields = zero how
can I make a zero appear? Right now the field just shows blank if it is equal to zero. I really want the zero to
appear on a form that I have created from the query. Thanks for all help.

Code:
SELECT MonthlyEcnReceived.Received, MonthlyEcnImplemented.Implemented, [MonthlyEcnW/Structure].[W/Structure], [MonthlyEcnW/StructureImplemented].Implemented
FROM MonthlyEcnReceived, [MonthlyEcnW/Structure], MonthlyEcnImplemented, [MonthlyEcnW/StructureImplemented];
 
If a field contains a 0 then it would display a 0. I suspect your field is Null instead. Look into the Nz() function.
 
Below is the sql of the query that is used for the
field that is show up bland instead of 0.] The part in
the quote is the actual field I am trying to sum. How would
I apply the Nz function here? Thanks for your help with
this!

SELECT Sum(IIf(([ECN Number]=True),1,0))


Code:
SELECT Sum(IIf(([ECN Number]=True),1,0)) AS Implemented
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number]) Not Like "c*" And (ECNBCNVIPtbl.[ECN Number]) Not Like "C*") AND ((ECNDetailtbl.[Actual Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])<>"Do Not Process"))
WITH OWNERACCESS OPTION;
 
I have read everything I can find. I just do not know how to apply it to this query
specifically and get the total count.

Code:
Expr1: Nz([ecnbcnviptbl].[ecn number],0)

I tried the code above but this lists out every ECN Number and not the count.

Thanks for your help.
 
Isn't it just a case of too many ()

Sum(IIf(([ECN Number]=True),1,0))

should be Sum(IIf([ECN Number]=True,1,0))

Brian

Edit No it will still work with the extra unnescessary (), however that statement cannot give a blank field if any data is selected by the where clause. If that's the case then Nz(Sum.......),0) as stated in NZ help, and further wrap Val round it if you want it numeric not text
 
Last edited:
So your issue is in the first line of the SQL statement? The [ecn number] field is either True (-1), False (0) or Null, right?
 
I received the below sql that works, thanks to everyone!

Code:
SELECT Nz(MonthlyEcnReceived.Received,0) AS EcnReceived
, Nz(MonthlyEcnImplemented.Implemented,0) AS EcnImplemented
, Nz([MonthlyEcnW/Structure].[W/Structure],0) AS [EcnW/Structure]
, Nz([MonthlyEcnW/StructureImplemented].Implemented,0) AS [EcnW/StructureImplemented]
FROM MonthlyEcnReceived, [MonthlyEcnW/Structure], MonthlyEcnImplemented, [MonthlyEcnW/StructureImplemented];
 

Users who are viewing this thread

Back
Top Bottom