Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    5

    Unanswered: Help with Nz or nulls in query

    I'm trying to run a report based on this query(qryCSAP_Crosstab).

    This particular field in the query (Not Tested) may not have any data and I'd like to display zeroes in the column.

    I'm struggling with the Nz function and just can't seem to get the syntax right. I'm not sure if it's because it's in a crosstab query or what.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    Exclamation

    ezpc,

    The test.zip database has linked tables and therefore cannot be opened.
    Regards,



    John A

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    NZ

    MyNameForResult: nz!(myFieldName, MyResultIfNull)

    ie

    UserName: nz!(User, "<null>"

  4. #4
    Join Date
    May 2003
    Posts
    5

    Red face Duh!

    So sorry about that!! Here's the file with the tables included.
    Attached Files Attached Files

  5. #5
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Lightbulb Alternative to NZ

    I use the following code to determine if the field is null or is empty and display 0.00 is null or empty:

    =iif([Field1] & "" = "","0.00",[Field1])


    Hope this helps

    Mark

  6. #6
    Join Date
    May 2003
    Posts
    5

    Unhappy Getting closer to Nz

    Mark, that code worked for me. However, I think I may not have it in the right query. It's giving me the zeros in my field but there is also an extra row showing up.

    There's something I'm still missing.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Try This

    I added a new query to your db called "qryCSAP_Ethnicity_Report_New". I added that function to each field and I put not null in the "tblEthn_Desc" to prevent that Null record from being displayed. I hope this is what you are looking to do.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2003
    Posts
    5

    Smile It's working!

    Mark, thanks so much for your patience. My report is working perfectly now. Hope I can help you sometime.

  9. #9
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    You're Welcome

    I glad I could help you. Did you design that db program? It seems pretty well written. What exactly is it for?


    Good Luck,

    Mark

  10. #10
    Join Date
    May 2003
    Posts
    5

    db Program

    The program is for schools to document and report assessment data. I actually created it a few years ago but then the state(CO) got a grant to build their own and distributed it to all the schools(for free). Since I couldn't really compete with free, I dropped the project. Now, a year or so later, the "free" isn't working out or very user friendly.

    So I'm back on the project. I feel like it's a little outdated now though and if you saw my(mostly undocumented) code you would probably laugh!

    Anyway, I hope to have it on my website in the next day or two as a download for demo purposes. The website is www.student-profiler.com if you want to check it out.

Posting Permissions

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