Is Not Null Criteria Problem

I posted a question a while back for help to fix my query so that I wouldn’t
have records showing with zero values for both months such Prod25 in the
following:.
Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $0

I had WHERE statement in my criteria for the $Cost:
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))... but was told to add the same criteria
to my other calculated fields(Mo1&Mo2). I did so and my column with the
zeros disappeared. I thought that was what I wanted but I noticed that if
any records contained $0, then that record did not show (e.g. Training for
Prod7 would not be showing).

Any ideas about how I can rid myself of the records that contain $0 for both
months, but keep the records that have cost for one month but not the other?

Here is my SQL that eliminates the record for Prod7:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0)
AND ((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0));

Thanks for looking at this and for any help!

AccessKay -

Change your HAVING clause so that it only exludes records where both Mo1 and
Mo2 are zero. This is simplified by using the nz() function so you don't
have to test separately for null and then for zero. Replace all of your
HAVING clause with this one (not tested):

HAVING (((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,nz([tblTrans_Mstr].[Lbr_Total_Cost],0),0)))
+ (Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,nz([tblTrans_Mstr].[Lbr_Total_Cost],0),0))) 0));


--
Daryl S


"AccessKay" wrote:

I posted a question a while back for help to fix my query so that I wouldn’t
have records showing with zero values for both months such Prod25 in the
following:.
Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $0

I had WHERE statement in my criteria for the $Cost:
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))... but was told to add the same criteria
to my other calculated fields(Mo1&Mo2). I did so and my column with the
zeros disappeared. I thought that was what I wanted but I noticed that if
any records contained $0, then that record did not show (e.g. Training for
Prod7 would not be showing).

Any ideas about how I can rid myself of the records that contain $0 for both
months, but keep the records that have cost for one month but not the other?

Here is my SQL that eliminates the record for Prod7:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0)
AND ((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0));

Thanks for looking at this and for any help!

Very clever Daryl. This works perfectly. Thank you so much.

"Daryl S" wrote:

AccessKay -

Change your HAVING clause so that it only exludes records where both Mo1 and
Mo2 are zero. This is simplified by using the nz() function so you don't
have to test separately for null and then for zero. Replace all of your
HAVING clause with this one (not tested):

HAVING (((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,nz([tblTrans_Mstr].[Lbr_Total_Cost],0),0)))
+ (Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,nz([tblTrans_Mstr].[Lbr_Total_Cost],0),0))) 0));


--
Daryl S


"AccessKay" wrote:

I posted a question a while back for help to fix my query so that I wouldn’t
have records showing with zero values for both months such Prod25 in the
following:.
Category DGroup Product Mo1 Mo2
Training Labor Prod1 $5,000 $6542
Develop Labor Prod25 $0 $0
Training Labor Prod7 $2395 $0

I had WHERE statement in my criteria for the $Cost:
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))... but was told to add the same criteria
to my other calculated fields(Mo1&Mo2). I did so and my column with the
zeros disappeared. I thought that was what I wanted but I noticed that if
any records contained $0, then that record did not show (e.g. Training for
Prod7 would not be showing).

Any ideas about how I can rid myself of the records that contain $0 for both
months, but keep the records that have cost for one month but not the other?

Here is my SQL that eliminates the record for Prod7:
SELECT tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product,
Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0))
AS Month_1, Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)) AS Month_2
FROM tblTrans_Mstr
WHERE (((tblTrans_Mstr.Lbr_Total_Cost) Is Not Null And
(tblTrans_Mstr.Lbr_Total_Cost)0))
GROUP BY tblTrans_Mstr.Category, tblTrans_Mstr.DGroup, tblTrans_Mstr.Product
HAVING (((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1])
And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo1]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0)
AND ((Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))
Is Not Null And (Sum(IIf([TransDate] Between
([Forms]![frmVarianceQtr]![txtMo2]) And
DateAdd("m",1,([Forms]![frmVarianceQtr]![txtMo2]))-1,[tblTrans_Mstr].[Lbr_Total_Cost],0)))0));

Thanks for looking at this and for any help!