If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Is Not Null Criteria Problem
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! |
#3
|
|||
|
|||
Is Not Null Criteria Problem
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! |
Thread Tools | |
Display Modes | |
|
|