Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: Simple criteria/conditional statement

    I have a table with a lot of blanks or null fields. I build a crosstab from this table. Now, I am trying to replace all the nulls with zeros. So I tried =iif(isnull([2004]),0,[2004]) in the criteria box in the crosstab, but an error message appeared said...you cannot issue a criteria in this field.

    error message: You cant specify criteria on the same field for which you entered value in the crosstab row.

    Anyhow know how to solve this problem?

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'd do it in a different order. Build a query to "clean up" the data from the table, then make the crosstab based on that query.

    Access has a built in function that makes this:
    =iif(isnull([2004]),0,[2004])
    a little easier to write...
    =NZ([2004],0)

    NZ means Null To Zero
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by RedNeckGeek
    I'd do it in a different order. Build a query to "clean up" the data from the table, then make the crosstab based on that query.

    Access has a built in function that makes this:
    =iif(isnull([2004]),0,[2004])
    a little easier to write...
    =NZ([2004],0)

    NZ means Null To Zero
    Hi,

    I am working with a large set of records, I am trying to avoid doing a query upon a query. Could I try doing some update queries to replace all the nulls with zeros in the maintable? If so, how do you do this?

    Also I inputted that expression into the criteria box, instead of replacing the nulls, it basically skips the nulls.
    Last edited by tialongz; 12-28-06 at 09:50.

  4. #4
    Join Date
    Dec 2006
    Posts
    53
    I use update queries all the time to clean up data. Do the following for each field you need to NZ.

    1. Create the query in design view.
    2. Add the table you need to update.
    3. Change the query to an Update Query by clicking the Query menu in deisng view.
    4. Add the field you want to NZ.
    5. Enter 0 for Update To.
    6. Enter Is Null for criteria.
    7. Click the Query menu, and click Run.

  5. #5
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by dfwelch
    I use update queries all the time to clean up data. Do the following for each field you need to NZ.

    1. Create the query in design view.
    2. Add the table you need to update.
    3. Change the query to an Update Query by clicking the Query menu in deisng view.
    4. Add the field you want to NZ.
    5. Enter 0 for Update To.
    6. Enter Is Null for criteria.
    7. Click the Query menu, and click Run.
    Thanks, it worked like a charm.

Posting Permissions

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