Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NZ function (Access 2000)

    I have made a botched NZ function. It does not give the results i want.For example in the coloumn -1 i have no record, while in column 0 i have 1 . The NZ function gives me the result -1, while the result should be 1.Can you help ?
    Diff: NZ([-1];0)-NZ([0];0)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NZ function (Access 2000)

    I have a terrible feeling that as you are using singular numeric values for field names it is confusing Access. The Diff field is seeing an equation of

    -1-0 = -1

    I strongly suggest that you rename your fields to text and not use numerics for this very reason
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ function (Access 2000)

    The field names -1 and 0 are the result of using a crosstab query with a Yes/No field as column header.
    Because there are square brackets [ ] around the field names, Access interprets them as field names, not as numbers.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ function (Access 2000)

    Diff appears to do exactly what you ask it to do.
    In the -1 column you have no record so value is 0.
    In the 0 column you have value 1.
    The result of 0 - 1 is -1.
    If you want the difference to be 1 you should switch the positions of [-1] and [0].

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ function (Access 2000)

    Yes, you are right, but then i didnt write it properly. I do not want to exchange places, but i do not want to substract when the first coloumn is 0, and i want a result 0-1 = 1

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ function (Access 2000)

    Try
    <code>
    Diff: Abs(NZ([-1];0)-NZ([0];0))
    </code>
    Note for others reading this: the semi-colon is the list separator on the user' system. Loungers in English-speaking countries should use a comma instead of a semi-colon.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ function (Access 2000)

    Just to let you know that your suggestion is great and solves all the problems !

Posting Permissions

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