If field is zero show 0 2

[netrusher]

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.

SELECT MonthlyEcnReceived.Received, MonthlyEcnImplemented.Implemented, [MonthlyEcnW/Structure].[W/Structure], [MonthlyEcnW/StructureImplemented].Implemented
FROM MonthlyEcnReceived, [MonthlyEcnW/Structure], MonthlyEcnImplemented, [MonthlyEcnW/StructureImplemented];

 

[HarleyQuinn]

The Nz() function?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[netrusher]

Harley,

How to I apply the Nz() function? Where does it go?

 

[HarleyQuinn]

On the fields you want to show as 0 instead of NULL use something like:

Nz(YourTable.YourField,0)

You would use this in your SELECT statement. So just wrap each field you need in it basically.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[netrusher]

Harley,

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))

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;

 

[HarleyQuinn]

Is [ECN Number] a Text field?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

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];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Yes it is a text field.

 

[HarleyQuinn]

If it's a text field you're trying to Sum if it's got a value in it, correct?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[netrusher]

Yes that is correct.

 

[netrusher]

PH,

Thanks for the post. That works just fine!

 

[netrusher]

Harley,

Thanks for your input. Did you have more to post for this?

 

[HarleyQuinn]

You're welcome, thanks for the star

No, I was originally meaning for you to do what PHV posted but when you posted the second query it threw me a bit and I went off on a tangent!

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.