Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    85

    Unanswered: dealing with null values

    Hey, Im trying to create a Total With a formula. I have a CrossTab Queary. I have the column heading as Months from my tbl and this is what it shows....Heres what i have:

    Jan Feb Mar Apr May Jun Jul.....Dec Total
    0.1 0.2 0.2
    0.1 0.2 0.3 0.1 0.01 0.2 0.3 0.14
    and soo on..

    Im dealing with null values but i cant seem to get the null formula to work and ideas? the formula for total is.... (R+1)*(Rn+1)

    Let me know if you can help or have any ideas thanks!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The function to use when dealing with null values is the nz() function, which requires two arguments: the variable name and the desired (corrected) value of the null field. If you're dealing with numerics, you want to correct the null to 0. If you're using strings, you want it to correct to a zero-length string, or "" (sometimes you need an actual space, [" "], but that's not for now).

    In each of the column headings, add the null function. e.g. If Jan is a heading, change it to
    Code:
    Jan: Nz(<variable name>, 0)
    Do the same for the other column names. Be aware that if Jan is the actual field name, you can't use it in both places, and you have to change the column name.

    Sam

  3. #3
    Join Date
    Jun 2012
    Posts
    85
    Thanks I got it to work!

  4. #4
    Join Date
    Jun 2012
    Posts
    85
    That worked great, any chance there is a way to tell the computer there is a zero there and be able to hide it? I have it nice presented into a report in a chart, but i dont want the zero to be there....

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Sure, in the report, though, nowhere else. In the report's Detail section, click anywhere on the top bar of the section. If the Property Sheet is closed, right-click on it instead and select Properties. In the Property Sheet, right-click on the line that says On Print. Form the dialog box, select Code Builder. That opens the VBA Editor. It will open the correct event automatically. Add the following lines of code into the proc.
    Code:
        If Me.<textbox name1> = 0 then
            Me.<textbox name1>.Visible = False
        Else
            Me.<textbox name1>.Visible = True
        Endif
    You will have to do this for all 12 textboxes in the Detail section. Just be sure you have the textbox names straight.

    Sam

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Sam Landy View Post

    If you're dealing with numerics, you want to correct the null to 0
    Since we're in an Educational Mode, as we frequently are, here, it should be noted that there is a specialized case, when dealing with Nulls and Numeric values, where you do not want to leave the 2nd Argument blank, nor set it to Zero! That case involves a situation where the resultant value will be used to Divide By! Dividing by Zero will always pop an Error 11, Division by Zero. So if you have something like

    CostPerUnit = TotalCost/Units

    you'd want to cover cases where Units is Null, but you wouldn't want to use

    Nz(Units, 0)

    because

    TotalCost/0

    would pop an error! If Units is left 'empty,' which is to say Null, you'd want the TotalCost to not be divided at all, or, in other words, to remain the same. And since any Number when divided by 1, remains the same, you'd want to use 1 instead of Zero.

    So, in this specific case, instead of

    CostPerUnit = TotalCost/Nz(Units, 0)

    you'd use

    CostPerUnit = TotalCost/Nz(Units, 1)

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I never came accross the situation, M., but you are right.

    Sam

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You know, Sam, Access VBA is so vast you can work in it for years and never come across a given situation; it just so happens that I was faced with this the very first time I used Nz()!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're right!

    After being neck-deep in Access for so long, I've learned that the correct way to handle your situation, if possible, is to obtain a report of all null values of all critical fields and to correct them before having to resort to Nz().

    In Mike's case, though, 0 is a valid number. That's why I told him to use it.

    Sam

Posting Permissions

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