Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reporting Question (97 SR2)

    I have the following formula in a text box on my report:
    =Trim(Format(Avg([DateComplete]-[DateStarted]),"#.#") & " Days")
    I would like for the formula to exclude any items which are not applicable... Right now, It's returning #error when I run the report.

    Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Reporting Question (97 SR2)

    >>I have the following formula in a text box on my report:
    =Trim(Format(Avg([DateComplete]-[DateStarted]),"#.#") & " Days")
    I would like for the formula to exclude any items which are not applicable... Right now, It's returning #error when I run the report. <<

    Make sure that you don't have controls named DateCompleted and DateStarted.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Reporting Question (97 SR2)

    Do you have any null values in either field or in both fields in a record? Adding or subtracting a Null value returns a null, and you can't average that, no matter how hard you try. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting Question (97 SR2)

    I'm sure I do, but i'm not responsible for how the data's entered, just how it works... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Is there any way to neglect null values when running averages? Or will I have to build in a statement at the data entry point?

    Thanks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Reporting Question (97 SR2)

    You could try using Nz like this:

    Trim(Format(Avg(nz([DateComplete]-[DateStarted],0)),"#.#") & " Days")

    However, I don't know whether that will throw the count off. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting Question (97 SR2)

    That's a good point.. the last thing I need is an inacurate report to give to management.. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I'll go the 'build statement' route... There have been points in the development of this monster when fields didn't populate automatically, perhaps it's choking on a remnant of that...

    Thanks Millions Charlotte! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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