Results 1 to 6 of 6

Thread: Report Problem

  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Report Problem

    All,

    Have a frustrating report problem. Have code of:
    Code:
    Function RAmount()
        HrsAmt = DLookup("trp_ham", "qryTIMshtS", "[trp_id]=" & Me![Row_ID])
        MilAmt = DLookup("trp_mam", "qryTIMshtS", "[trp_id]=" & Me![Row_ID])
        RAmount = HrsAmt + MilAmt
    End Function
    Function RCount()
        RCount = DCount("trp_id", "qryTIMshtS")
    End Function
    Function RTotal()
        HrsTot = DSum("trp_ham", "qryTIMshtS")
        MilTot = DSum("trp_mam", "qryTIMshtS")
        RTotal = HrsTot + MilTot
    End Function
    Three fields are on the report with:

    =RAmont()
    =RCount()
    =RTotal()

    The middle function works, but the other 2 do not. Can not, for the life of me, figure out why one works and the others do not. I spent over 4 hours now using every possible combination I know to make these work.

    HELP!!!!

    DBS4M

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Are you sure you don't have any nulls in those fields? Use the Nz() function and see if that helps.

    Also, I usually specify the type of data a function returns, like
    Function RTotal() as Double
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Tried type conversions on those DLookups?

    I see nothing defining data types, so that's my guess.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    What I found

    All,

    Found, contrary to normal math, that reports will not work if there is a null value anywhere. So code now reads:
    Code:
    Public HrsAmt, MilAmt, HrsTot, MilTot
    Function RAmount()
        Dim FullAmount
        FullAmount = 0
        HrsAmt = DLookup("trp_ham", "qryTIMshtS", "[trp_id]=" & Me![Row_ID])
        If HrsAmt > 0 Then FullAmount = FullAmount + HrsAmt
        MilAmt = DLookup("trp_mam", "qryTIMshtS", "[trp_id]=" & Me![Row_ID])
        If MilAmt > 0 Then FullAmount = FullAmount + MilAmt
        RAmount = FullAmount
    End Function
    Function RCount()
        RCount = DCount("trp_id", "qryTIMshtS")
    End Function
    Function RTotal()
        HrsTot = DSum("trp_ham", "qryTIMshtS")
        MilTot = DSum("trp_mam", "qryTIMshtS")
        RTotal = HrsTot + MilTot
    End Function
    Which works.

    Thanks all!!

    DBS4M

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by dbsupport4me
    Found, contrary to normal math, that reports will not work if there is a null value anywhere.
    It's not contrary to math. Remember that a null DOES NOT EQUAL zero. If you've ever programmed in a mid-level or low-level language before (such as C++ or something) than you know what a pointer is, and so you can understand better what a null is. Sometimes I think it's dangerous to let people program on a high-level language without first understanding a lower-level language, so as they understand what exactly the language is doing for them.

    Bottom line, that's why you use Nz() function (or an IIF statement in a query). Or better yet, don't let yet your user leave null fields if it can be helped.

    Cheers!
    Me.Geek = True

  6. #6
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Data Control

    Nick,

    Since this is on-going DB dev and that has caused some fields to fill as Nulls the user has agreed to take responsibility for the nulls and fix these, but in the mean time the report has to work. Proof of Concept ya know.

    Anyway thanks for input, I think I need to go back and set default values to "0" for these field, so they do not get nulls.

    Thanks again!

    DBS4M

Posting Permissions

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