Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: NZ not working for me...

    I have the following statement in a subform:

    =NZ(Query!Jan,0)

    The problem is, if there is no data in that query for the month of Jan its giving me a #Error, it should give me a 0 right? In the subform there are 5 different query catagories on the x axis and on the y axis is JAN - Dec. If there is data in the month of Jan for 1 query it will show the number for JAN and Feb-Dec will show 0's (because no data has been entered for Feb-Dec). If there is no data for Jan in it will give me the #error for Jan-Dec. I tried do a +0 to after the ( ), to see if it would add 0, but that doesn't work either. Has anyone had this issue before?

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    try "0" if this is not a true integer field it is a string

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    Thanks for the suggestion, but I've tried "0" as well, but it doesn't work. Btw, the queires that are giving the #Error do not have any data in them.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I did not really look before but the syntax is does not seem to be correct

    NZ(Query!Jan,0)


    you need to to maybe do a dlookup and then handle the nulls within that function

    suppose ytou are looking for a value in a query and if that value is null you want to return a zero

    dim x integer 'if this a number field
    x = nz(dlookup("[myfiled]","myquery", _
    & " myid = forms!myformname!myfiield"),0)

    this function looks for a value in a field where the id criteria is equivalent to the id on a field on a form. If the value is null a
    zero (0) is returned

    I hope this helps

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    OR
    You could just add the Nz() functions in the field design of the query itself... That's how I normally do it...

    Just a thought...

  6. #6
    Join Date
    Feb 2004
    Posts
    11
    In my query I have the following exp: Expr1: Format([Issue Date],"mmm"), so I can get Jan - Dec. I tried the following, Expr1: NZ(Format([Issue Date], "mmm"),0)+0. Put the +0 on there because for some reason NZ(expr, 0) won't return a 0, and adding a +0 will. I tried that in the query and I'm still getting a #Error. Even tried it with the NZ expression in the query and the form just stating =Query!Jan. Still get the #Error.
    Last edited by Decemuirs; 02-10-04 at 12:07.

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Decemuirs
    In my query I have the following exp: Expr1: Format([Issue Date],"mmm"), so I can get Jan - Dec. I tried the following, Expr1: NZ(Format([Issue Date], "mmm"),0)+0. Put the +0 on there because for some reason NZ(expr, 0) won't return a 0, and adding a +0 will. I tried that in the query and I'm still getting a #Error. Even tried it with the NZ expression in the query and the form just stating =Query!Jan. Still get the #Error.
    Ummm... First of all, in your control source you've chosen a field called "Jan" but in the query you've given the field the name "Expr1"...Does the query return the 0 where you want it? ...in Expr1? ... If so, try setting the control source to "=Query!Expr1 ...

    Second... I just noticed... In this response you've said that you are using the expression "Expr1: NZ(Format([Issue Date], "mmm"),0)+0", but in your original post you said "If there is data in the month of Jan for 1 query it will show the number for JAN"... Why are you trying to format a number to a "mmm"??

  8. #8
    Join Date
    Feb 2004
    Posts
    11
    Ok, figured out the solution. I dropped the NZ expression and did an iif statement in the form. iif(isNumeric(Query!Jan), Query!Jan, 0). Works like a charm. So, if there is no data in the Query it will give me a 0 instead of that darn #Error. Thanks for your suggestions. Sorry I confused you with this post above. Thanks again.

Posting Permissions

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