NZ function in a crosstab 1

[dday01]

Help!!! I created a crosstab query that displays values by month. I don't want any blanks in the report so I changed the quantity field to an expression using the Nz function and I am using 0 as the fillin value. I am still getting blanks in the result for some of the months. Anyone have any ideas?

Thanks

D

 

[vikin9]

Did you use the wizard to create the Crosstab? The wizard automatically places months ("Jan", "Feb", "Mar"...) in the Query Properties-Column Headings box. This presets what columns are shown and determines their order in the query output. And, it sums values of months from different years (aaah!). That's probably where your blank columns are coming from. I deleted the months and used a date format instead (Expr1: Format([Date],"yyyy-mm&quot)in the Column Heading field. This sorts the months in order (like for a graph), correctly summarizes data beyond one year and only includes a column if there is data present(like you want).

 

[dday01]

Thanks Vikin, that sounds good if I do reports over a multi year period. The data for this query is only over the course of one year, and for one record or another there will always be data in each of the columns. The problem is that there are some records which may only have data in 8 of the 12 month columns, these show up as blanks where as I need them to show up as zeros. Any help would be greatly appreciated.

Thanks

D