Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting a text box value (AXP)

    Hello,
    I have a control on a subform that won't calculate the way I want it to. Here is what I have so far for the control source property:

    =IIf(IsNull(Forms!frmmatrixtabs!frmsummary!frmstan dardbom!text13),0,DSum("cost_01","qrystandardsysbo m"))
    (note: I had to remove any brackets because they get truncated on this message board)

    When there is valid data, the control calculates properly. However, because there won't always be data for this item, I want a 0 there when there is none. And the reason is that there are other higher level calculations going on that need to see a value in that box. What am I doing worng?

    Thanks,
    Mark

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

    Re: Setting a text box value (AXP)

    Do you mean that even if Text13 is not null, DSum(...) could still be null? In that case, you need another IIf(IsNull(...)), or Nz:

    =IIf(IsNull(Forms!frmmatrixtabs!frmsummary!frmstan dardbom!text13),0,Nz(DSum("cost_01","qrystandardsy sbom"),0))

    If I misinterpreted your question (which is quite possible), post back.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a text box value (AXP)

    I would like the value to be zero if the query (qrystandardsysbom) reuturns no results. If the query returns some rows, the control calculates properly. Other calculations on the form need to see at least a 0 otherwise they will fail with #error.

    Thanks,
    Mark

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

    Re: Setting a text box value (AXP)

    Doesn't the Nz(DSum("cost_01","qrystandardsysbom"),0) bit do just that?

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a text box value (AXP)

    For some reason, I can't get it to populate with a 0. It does calculate with good query data though. I have checked everywhere else (I think!) and it still doesn't work.

    Thanks,
    Mark

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Setting a text box value (AXP)

    You said, <<It does calculate with a good query>> Thus, I would assume a bad query that has null values returns no values. Check the query generating the data for the DSum for null values. Use the Nz function in that query to return zeroes prior to passing the data to the Dsum.

    HTH
    Regards,

    Gary
    (It's been a while!)

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a text box value (AXP)

    OK. Thanks Gary!

    Mark

Posting Permissions

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