locked
Nz function returns text field from number field RRS feed

  • Question

  • I'm using the Nz function to convert null values to the value zero so I can include these in calculations.  The output field from the expression using Nz always ends up being a text field.  Is there a way to control this?
    Tuesday, August 31, 2010 3:01 PM

Answers

  • BDB,

    You are still free to type in a named format type (i.e. "General Number" / "Currency" / "Percent") or a valid user-defined format string (i.e. "####", "###.#", "0000", etc.,.) in that location.

     

     

     

    • Marked as answer by BDB_Laf Tuesday, August 31, 2010 5:37 PM
    Tuesday, August 31, 2010 4:45 PM

All replies

  • maybe post your code.  I don't have any problems using the return of nz in calculations when the original value  is null
    Tuesday, August 31, 2010 3:14 PM
  • First, could you post a sample of the query where you are using the function?

    Secondly, the NZ function can return a value of ANY type (with the possible exceptionof an object, except when used via VBA).

    So, you could use NZ(<whatever expression>, "0") to return a ZERO value as a text string, or NZ(<whatever expression>, 0) to return ZERO as a numeric value.

    HTH.

    Tuesday, August 31, 2010 3:16 PM
  • I just tested this and ?nz(Null,0) = "0" in the immediate window is true.  However either way... I also did this in the immediate window with no problems...

    ?nz(Null,"5") +1
     6

    this was unexpected, but it worked to do calculations

    Tuesday, August 31, 2010 3:22 PM
  • syzyq,

    I don't know what you mean:

    ?vartype(nz(null,0))
     2 'vbInteger

    I think that VB is performing "Evil Type Coersion" on your ?NZ(null,0) = "0" test, which leads to the True result.

     

    to amplify:

    ?nz(null,0) = "Zaphod"
    False
    ?nz(null,0) = "0"
    True
    ?nz(null,0) = "1"
    False

    Tuesday, August 31, 2010 3:32 PM
  • which is what I expected, however that didn't really matter because when I returned it as a string - it was still able to do a calculation - not sure what I am missing, but that's really off topic.  We still need to see the op's code
    Tuesday, August 31, 2010 3:36 PM
  • Thanks for the replies.  I'm a neophyte and this is probably almost embarrassingly simple to most of you, but thus we learn.

    Below is the code. "Gas Cum" is numeric, "CumGas" always comes back as text.

    CumGas: Nz([Gas Cum],0)

    • Edited by BDB_Laf Tuesday, August 31, 2010 3:38 PM mistype
    Tuesday, August 31, 2010 3:37 PM
  • BDB,

    How is [Gas Cum] defined in the underlying table?

     

    Tuesday, August 31, 2010 3:38 PM
  • ?nz(null,0) = "Zaphod"
    False
    ?nz(null,0) = "0"
    True
    ?nz(null,0) = "1"
    False

     

    agreed - I just wouldn't expect 0 = "0" to be true nor would I expect nz(Null,"5") + 1 to do anything but error out - therein lies my surprise

    Tuesday, August 31, 2010 3:41 PM
  • suzyq,

    That is why it is referred to as "Evil type coersion" - it can lead to unanticipated results.

    It is a consequence of VBA not being a "strongly typed" programming language.

    Tuesday, August 31, 2010 3:44 PM
  • I'm assuming you are using the query builder?  right click on your CumGas field and select format, then select number - that may help
    Tuesday, August 31, 2010 3:44 PM
  • forgive me - VBA is not my native language :)
    Tuesday, August 31, 2010 3:47 PM
  • [Gas Cum] is a number in the source table.
    Tuesday, August 31, 2010 3:49 PM
  • "VBA is not my native language"

     

    Well, if you're going to be an Access developer, it is now! ;-)

    Tuesday, August 31, 2010 3:50 PM
  • I tried this but when I bring up 'field properties' for CumGas (the new field created using the Nz function) and go to the dropdown menu for format, I get a blank box.  What's up with that?
    Tuesday, August 31, 2010 4:29 PM
  • is CumGas a field whose type is already set or are you creating that field, because as long as the format isn't already set (say by the field type in the table) then you should get a list to select from. - what type is [Gas Cum]
    Tuesday, August 31, 2010 4:40 PM
  • BDB,

    You are still free to type in a named format type (i.e. "General Number" / "Currency" / "Percent") or a valid user-defined format string (i.e. "####", "###.#", "0000", etc.,.) in that location.

     

     

     

    • Marked as answer by BDB_Laf Tuesday, August 31, 2010 5:37 PM
    Tuesday, August 31, 2010 4:45 PM
  • And what's the problem in converting

    CumGas: Nz([Gas Cum],0)

    to

    CumGas: CInt(Nz([Gas Cum],0))


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, August 31, 2010 4:49 PM
  • Thanks for your help, all. 

    I've got what I need.  Still have some questions but I've learned a few tricks, too.

    Tuesday, August 31, 2010 5:40 PM
  • I had the same problem. You can type:

    CumGas: Nz([Gas Cum],0)+0 

    and you will get the the number as returned value..

    • Proposed as answer by Bitmapped Wednesday, November 28, 2018 4:59 PM
    Tuesday, October 7, 2014 7:07 PM
  • @rommyt: Thanks for this tip. This was the solution that worked for me. Cint() still returned text and Access wasn't giving me any types to choose in the field properties.
    Wednesday, November 28, 2018 5:00 PM