nz function up the creek - repeats value from 1st field 1

[bhoran]

I have a crosstab query which builds its fields using a DateDiff function.

I then use a standard append query over the crosstab to append the data. in this query I use the Nz function to return nulls as 0. However, if I use the Nz function in only the 1st field it works fine but if I use it in the subsequent fields it simply populates reach field with the value of the 1st field.

I need to either use another funtion to return nulls as zero or separate code to update nulls as zero after the append has occurred. - I can do this field by field in code but can anyone show me how to do all fields at once or with 1 piece of code?

Any help is appreciated.

 

[bhoran]

Ok I have created a function to update the nulls (easier than I thought) but I would still like to know whay the nz function does not work.

Cheers

 

[dhookom]

bhoran,
It would help if you included your SQL view and what your results are and why they aren't what you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

sorry Duane,

here is the crosstab which works fine.

PARAMETERS [Forms]![frmUpdateWklyForecastStartDate]![cmbStartDate] DateTime;
TRANSFORM Sum(Round([SumOfCases],2)) AS [Case]
SELECT UpdateWklyForecastUnion.Brand, UpdateWklyForecastUnion.pgcode, UpdateWklyForecastUnion.PromoGroup, UpdateWklyForecastUnion.Account, UpdateWklyForecastUnion.State
FROM UpdateWklyForecastUnion
GROUP BY UpdateWklyForecastUnion.Brand, UpdateWklyForecastUnion.pgcode, UpdateWklyForecastUnion.PromoGroup, UpdateWklyForecastUnion.Account, UpdateWklyForecastUnion.State
PIVOT "Wk" & DateDiff("ww",[Forms]![frmUpdateWklyForecastStartDate]![cmbStartDate],[Start_Date]) In ("Wk0","Wk1","Wk2","Wk3","Wk4","Wk5","Wk6","Wk7","Wk8","Wk9","Wk10","Wk11","Wk12"

here is the append that is giving me grief

INSERT INTO FCASTENTRYWKLY ( Brand, PGCODE, PromotableGroup, Account, State, WK1, WK2, WK3, WK4, WK5, WK6, WK7, WK8, WK9, WK10, WK11, WK12, WK13, LstUpdtUser, LastUpated )
SELECT UpdateWklyForecastCrosstab.Brand, UpdateWklyForecastCrosstab.pgcode, PROMOTABLEGROUP.PromotableGroup, UpdateWklyForecastCrosstab.Account, UpdateWklyForecastCrosstab.State, nz([Wk0],0) AS Wk1, nz([Wk1],0) AS Wk2, nz([Wk2],0) AS Wk3, nz([Wk3],0) AS Wk4, nz([Wk4],0) AS Wk5, UpdateWklyForecastCrosstab.Wk5 AS Wk6, UpdateWklyForecastCrosstab.Wk6 AS Wk7, UpdateWklyForecastCrosstab.Wk7 AS Wk8, UpdateWklyForecastCrosstab.Wk8 AS Wk9, UpdateWklyForecastCrosstab.Wk9 AS Wk10, UpdateWklyForecastCrosstab.Wk10 AS Wk11, UpdateWklyForecastCrosstab.Wk11 AS Wk12, UpdateWklyForecastCrosstab.Wk12 AS Wk13, CurrentUser() & "" & "Admin" AS LstUpdtUser, Now() AS LastUpated
FROM UpdateWklyForecastCrosstab INNER JOIN PROMOTABLEGROUP ON UpdateWklyForecastCrosstab.pgcode = PROMOTABLEGROUP.PGCODE;

I have only used the nz function on the 1st 5 weekly fields in this example but originally it was on all wks.

Using the NZ function the Wkly fields return the same value as in Wk1 for every field, when I use the nz function only on Wk1 it works fine. However there are no null values in Wk1. The 1st Null value occurs in Wk2. I assume this is where the problem lies.

The crosstab is displaying a number of records in a different format where there is a null no record exists at all (rather than null value existing) so perhaps that is the problem?

 

[dhookom]

Just try change the Crosstab to use:
PARAMETERS [Forms]![frmUpdateWklyForecastStartDate]![cmbStartDate] DateTime;
TRANSFORM Val(Nz(Sum(Round([SumOfCases],2)),0)) AS [Case]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bhoran]

Thanks Duane,

Simple when you know how.