Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nulls in Crosstab queries (Access 97)

    I have a crosstab query which has a row header of dates, and column headers of various types of goods, and a value of goods booked out. Some dates don't have goods within a column header booked out, so I get a Null. I have tried various ways of getting the Value within the crosstab to display 0, instead of Null without success. When I try to add columns later, where there is a null value in one column field, I get an overall Null despite the fact that one of the fields has a value ..... can anyone help??
    Regards and thanks. <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls in Crosstab queries (Access 97)

    You can use the Nz function to substitute a 0 for null values: in the query design grid, use Nz([Fieldname],0) instead of [Fieldname].

    Does this solve your second problem too? If not, how are you adding columns?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nulls in Crosstab queries (Access 97)

    Hello Hans, how are you? Thanks for your reply. Had tried NZ in the value column with no success, but have now found another way to get to the data I was wanting. I'm not sure why NZ doesn't seem to work in crosstab queries.... Many thanks for your help as always.

    M

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls in Crosstab queries (Access 97)

    Marion,

    Since you already found another solution, this is just for the record - perhaps someone else will be able to use this. Nz *does* work in crosstab queries, but in a slightly different way. Enter an expression in the field of the form

    V: Nz(Sum([FieldName]),0)

    and set the Totals option to Expression.

    Have a good weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •