Need zero to show in query where null 5

[netrusher]

I have combined 4 queries to make this one query. Some of
the fields are blank because they is no information for
some of the dates. How can I get zero's to show up in these
blank fields?

SELECT 
    LeaksDateQry.DateField, 
        [LeaksWorkUnitsDayPct-1-3TCombine].Truck, 
        [LeaksWorkUnitsDayPct-1-3TCombine].[WU Totals], 
        [LeaksWorkUnitsDayPct-1-3TCombine].[Leak Totals], 
        [LeaksWorkUnitsDayPct-1-3TCombine].Percent, 
            [LeaksWorkUnitsDayPct-4-5TCombine].Truck, 
            [LeaksWorkUnitsDayPct-4-5TCombine].[WU Totals], 
            [LeaksWorkUnitsDayPct-4-5TCombine].[Leak Totals], 
            [LeaksWorkUnitsDayPct-4-5TCombine].Percent, 
                [LeaksWorkUnitsDayPct-6-9TCombine].Truck, 
                [LeaksWorkUnitsDayPct-6-9TCombine].[WU Totals], 
                [LeaksWorkUnitsDayPct-6-9TCombine].[Leak Totals], 
                [LeaksWorkUnitsDayPct-6-9TCombine].Percent
FROM 
    LeaksDateQry 
LEFT JOIN 
    (([LeaksWorkUnitsDayPct-1-3TCombine] 
LEFT JOIN 
    [LeaksWorkUnitsDayPct-4-5TCombine] ON [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate = [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate) 
LEFT JOIN 
    [LeaksWorkUnitsDayPct-6-9TCombine] ON [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate = [LeaksWorkUnitsDayPct-6-9TCombine].TodaysDate) ON LeaksDateQry.DateField = [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate;

 

[lespaul]

use the NZ function....I think it's:

NZ(FieldName, ValueIfNull)

Leslie

Have you met Hardy Heron?

 

[SoCalAccessPro]

Yup,

Example:

NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Truck])




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[netrusher]

Below is the code that I put in based on your suggestions.
Probably I have done something wrong. I still am getting
blanks insteand of 0's when the field is empty.

SELECT LeaksDateQry.DateField, 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Truck]) AS [1-3T], 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[WU Totals]) AS WU1, 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Leak Totals]) AS Leak1, 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Percent]) AS PCT1, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Truck) AS [4-5T], 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[WU Totals]) AS WU2, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[Leak Totals]) AS Leak2, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Percent) AS PCT2, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Truck) AS [6-9T], 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[WU Totals]) AS WU3, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[Leak Totals]) AS Leak3, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Percent) AS PCT3
FROM 
LeaksDateQry 
LEFT JOIN 
(([LeaksWorkUnitsDayPct-1-3TCombine] 
LEFT JOIN 
[LeaksWorkUnitsDayPct-4-5TCombine] ON [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate = [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate) 
LEFT JOIN 
[LeaksWorkUnitsDayPct-6-9TCombine] ON [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate = [LeaksWorkUnitsDayPct-6-9TCombine].TodaysDate) ON LeaksDateQry.DateField = [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate;

 

[PHV]

The Nz function needs 2 parameters.

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

 

[Remou]

You should use the syntax lespaul suggested.

 

[netrusher]

Thanks everyone,

Below is the code I am using and it is giving me zero's
where null.

Now I have another problem, the results of my PCT are showing up like 6.66666666666667E-02. Before this
would show up like 6.66. I went to properties for that
field in the query but under format is blank and I do
not know how to switch this to two decimal points. Any
advice?

SELECT 
LeaksDateQry.DateField, NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Truck],0) AS [1-3T], 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[WU Totals],0) AS WU1, 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Leak Totals],0) AS Leak1, 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Percent],0) AS PCT1, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Truck,0) AS [4-5T], 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[WU Totals],0) AS WU2, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[Leak Totals],0) AS Leak2, 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Percent,0) AS PCT2, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Truck,0) AS [6-9T], 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[WU Totals],0) AS WU3, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[Leak Totals],0) AS Leak3, 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Percent,0) AS PCT3
FROM 
LeaksDateQry 
LEFT JOIN 
(([LeaksWorkUnitsDayPct-1-3TCombine] 
LEFT JOIN 
[LeaksWorkUnitsDayPct-4-5TCombine] ON [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate = [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate) 
LEFT JOIN 
[LeaksWorkUnitsDayPct-6-9TCombine] ON [LeaksWorkUnitsDayPct-4-5TCombine].TodaysDate = [LeaksWorkUnitsDayPct-6-9TCombine].TodaysDate) ON LeaksDateQry.DateField = [LeaksWorkUnitsDayPct-1-3TCombine].TodaysDate
GROUP BY 
LeaksDateQry.DateField, NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Truck],0), 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[WU Totals],0), 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Leak Totals],0), 
NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Percent],0), 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Truck,0), 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[WU Totals],0), 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].[Leak Totals],0), 
NZ([LeaksWorkUnitsDayPct-4-5TCombine].Percent,0), 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Truck,0), 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[WU Totals],0), 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].[Leak Totals],0), 
NZ([LeaksWorkUnitsDayPct-6-9TCombine].Percent,0);

 

[PHV]

Perhaps this ?
Format(NZ([LeaksWorkUnitsDayPct-1-3TCombine].[Percent],0),"PERCENT") AS PCT1,

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

 

[netrusher]

Thanks PHV, that worked great!

Thanks to everyone who helped with this.