Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    nz function (Win ME/Access 97)

    The help articles specify that this function works on a variant field only, and yet I seem to see it used with any date type. It seems to work for me with currency fields, for example. Could you clarify?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: nz function (Win ME/Access 97)

    It seems to work with anything.

    I was going to ask another question about nz.
    A few times lately, I have found that nz returns a string when I want it to return a number.

    e.g. nz([somefield],0) + nz([someotherfield],0) = 11 when both fields are 1

    whereas val(nz([somefield],0)) + Val(nz([someotherfield],0)) = 2 which is what I want.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nz function (Win ME/Access 97)

    There's no such thing as a variant field. There is a variant variable type.

    Yes, it will work on text, number, datetime, etc. fields. It doesn't make much sense to use it on a non-variant variable, because you need a variant variable to hold a null.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nz function (Win ME/Access 97)

    I did not understand the statement that only a variant data type can hold a null. I use the NZ function to insure that I get a valid sum even when one of the fields contains a blank, which I thought was called a null value. For example, I built a test table with pairs of Lng Int , Dbl, and Currency data types. When one of the fields is blank, the sum is blank, but the use of the NZ in the blank field gives me the correct sum. Could you clarify your statement about the variant type being the only type that can have a null value? Is a null the same as a blank?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: nz function (Win ME/Access 97)

    No, a null is different than a blank. A blank is also referred to as a "zero length string" and can only appear in fields that allow character type data. A null on the other hand is literally no data, and can exist in any field where the required property is not set. So a numeric field can have a null value if required is not true, and in that case you definitely want to use Nz if you are doing arithmetic on the field. However the Nz function is more powerful in that you can use it with character data as well, and specify the value to be substituted for a Null.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: nz function (Win ME/Access 97)

    Null and "blank" are not the same thing at all. You can compare a zero-length string to something else, but you can't compare Null to anything, not even another Null because a comparison to Null will *always* return a False value. In the same vein, IsEmpty and IsMissing are not the same thing as IsNull--they all apply to different conditions.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: nz function (Win ME/Access 97)

    I agree entirely that a zero length string is not the same as Null.

    The question here is about blank. As I said before I don't think "blank" is a technical Word, so we probably won't all use it the same way.

    I see from Help in Access 97 that there it is taken to mean either Null or zero length string,

    "About working with blank fields in queries"
    "When a field contains no values, it contains a Null value or, for Text, Memo, or Hyperlink fields, a Null value or a zero-length string. "
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: nz function (Win ME/Access 97)

    Any field, of any type, can be Null and will be Null if it has not been assigned a value.

    When designing a table, there is no variant type. However, there is a variant type for variables defined in code, and only the variant type of variable can hold a Null.

    The following code will produce an error if the field test is Null
    <pre>Dim strTest As String
    strTest = Me![test]
    </pre>

    The following will not:
    <pre>Dim strTest As Variant
    strTest = Me![test]
    </pre>


    What does "blank" mean. It is not a technical word so it probably means different things to different people. I think using blank to mean Null is reasonable, although clearly Wendell want to use it to mean zero length string.
    Regards
    John



  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: nz function (Win ME/Access 97)

    A lot of the confusion comes from people being used to Excel, where IsBlank has meaning. There isn't a real equivalent in Access, but IsNull comes closest. A field may be null, but fields with default values are *never* null unless the default value was added after some of the records were already populated. The problem with trying to use the concept of "blank" is that it is a visual concept and you can't tell by looking whether a field is Null or contains a string--either a zero-length string or spaces. Both of those look empty but neither is actually Null.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nz function (Win ME/Access 97)

    You're getting confused over what fields can hold vs. what variables can hold. As I said, there is no such thing as a variant field; a numeric or string field can hold a null value. A variant variable (or equivalently, a variable with the variant data type) can hold any type of data including nulls. A non-variant variable (e.g. string, integer, long integer, etc.) can not hold a null.

    Hence, assigning an integer (for example) field containing a null to an integer variable will result in an error, often circumvented by the NZ function. Assigning an integer field containing a null to a variant variable will not result in an error. As well, arithmetic or comparisons (other than ISNULL) on null fields or null variant variables results in null.

Posting Permissions

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