Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    San Francisco
    Posts
    5

    Question Unanswered: Null values in Cross Tab queries

    I have a crosstab query that produces a table with a large number of Null values. They prevent me from summing up the crosstab collumnar fields further down the road without doing dreaded =iif(isnull(field1),0,(field1))

    Does any one have any suggestions on how to handle possible Null values in MS Office XP(2000)?

    Apreciate the help!

    Max

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    You need to look up the wonderfull nz() function
    so instead of
    sum(iif(isnull(field1),0,(field1)))
    you have
    sum(nz(field1))

  3. #3
    Join Date
    Jul 2003
    Location
    San Francisco
    Posts
    5
    null to zero shortens the formula that I have to use later on, but I am looking for something that would allow me to use it only once (somwwhere areound the VALUE section of the crosstab design), so that all the nulls that appear will be converted to zeros.

    here is sample of a query I am working with:


    Field1 Field2 Filed3
    ShotA 2D 5
    ShotA 3D 11.23
    ShotB 3D 15.5
    ShotB 3DAnim 12.5
    ShotB CG 0.23

    As a result the tab query will Null values for ShotA in
    Column fields 3D Anim and CG, and ShotB will have a Null
    value in field 2D.

    Any thoughts?

    Thanks,

    Max

    Originally posted by Risky
    You need to look up the wonderfull nz() function
    so instead of
    sum(iif(isnull(field1),0,(field1)))
    you have
    sum(nz(field1))

Posting Permissions

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