Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Question Unanswered: Null values in a calculated field

    I'm recording the years romano-british tombstones were found and wanted to make a pie chart showing which century they were found. I set up a query with the following calculated field:

    Expr1: Left([Year Found],2) + "00s"

    But the null values appeared in my pie chart as "Slice 1" so I tried to modify the expression and came up with

    Expr1: Nz(Left([Year Found],2))+"00s"

    The null values are now appearing as 00s in the pie chart which is better than 'Slice one' but is there any way I can alter the expression to get them to appear as "Unknown".

    Thanks

    Katie

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    iif(nz([Year Found]),="","Unknown",Left([Year Found],2) & "00s"))

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Rogue, I think you have too many commas in your code. The comma after the NZ seems to be an extra one. I would also recommend the following minor change:

    IIf(nz([Year Found],"")="","Unknown",Left([Year Found],2) & "00s"))

    That way if it is null then you are returning the Null string and not zero.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Thanks DC, you are absolutely correct...that post is downright embarrassing now that I look at it.

  5. #5
    Join Date
    Nov 2010
    Posts
    7
    Thanks for the help!

Posting Permissions

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