Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003

    Question Unanswered: Putting 0's in Null Fields in a Crosstab Query

    I have a table and related crosstab query based on the following info (simplified for ease of reading here):
    Candy Bar
    Date Sold
    UPC number of the sold bar

    Basically, I've created a crosstab with different "candy bars" in the rows and Date sold as the columns, using UPC as the "data". In a 100 day period, at least 1 candy bar is sold every day, although not every brand is. My crosstab shows how many of each candy bar was sold in that day. HOWEVER, I want the query to fill out the blank (null) fields with a 0 (if that particular candy bar brand wasn't sold on that day - There is no "0" entry in the table, rather nothing was entered for that day) . For example, a Crunch bar may have been sold yesterday, but a Hershey bar was not. Any insight into a solution would be much appreciated!

  2. #2
    Join Date
    Jan 2003
    You can use a CASE statment to do this.


    (CASE WHEN yourfield IS NULL THEN 0 ELSE yourfiled END)

    Use that in your select query and it will give you what you want.


  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    iif(isnull([numberSold]), 0, [numberSold])


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    guys, those "when null" solutions only work for actual nulls that are there, the problem that wukongeroo has is that there are missing entries, i.e. nulls that aren't there, if you know what i mean

    i have a solution, but it's ugly

    in order to "generate" a null, that you can turn into a 0, you have to do a left join

    from what? from a cross join of all combinations

    example on request, if it's really necessary

    wukongeroo, the blanks are really okay, the zeros would simply clutter up the crosstab

    as tufte might say, the zeros just get in the way


Posting Permissions

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