#Error on a Report

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • David Davis
    New Member
    • Mar 2008
    • 41

    #1

    #Error on a Report

    Hello,

    I need help on getting rid of #Error in my fields on a report when no data exist. I was using the below formula:

    ="" & [System] //is a field of text type from table1

    and when #Error showed I switched over to

    =IIf(IsNull([System])," ",[System])

    and that came up with the same results. If data exist it works fine but when data is not there I want it to be blank or say "No data exist".

    What formula can I use in the control source on this report to fix this. Any assistance would be most appreciative.

    Thanks
  • Stewart Ross
    Recognized Expert Moderator Specialist
    • Feb 2008
    • 2545

    #2
    Hi David. You have double-posted a thread on the same topic, which is not appropriate nor courteous to the respondents (see http://www.thescripts.com/forum/thread783265.html).

    If you are using a calculated control on a report make sure that its name is not set to be the same as the field you are referring to. You mention [system], so make sure the calculated field's name is not also system. You will undoubtedly receive a #Error if you try to refer in a calculated control to a field name which is the same as the control itself - it's a circular reference. Name the calculated textbox something else such as TheSystem or whatever you think is appropriate, just make sure it is a different name from that of the field you are testing.

    In its control source property just enter =Nz([system]) to return a blank if the system field is null.

    If you want to return a more specific message, then change this to
    =Nz([system], "Field is null") or whatever suits.

    -Stewart

    Comment

    • David Davis
      New Member
      • Mar 2008
      • 41

      #3
      Originally posted by Stewart Ross Inverness
      Hi David. You have double-posted a thread on the same topic, which is not appropriate nor courteous to the respondents (see http://www.thescripts.com/forum/thread783265.html).

      If you are using a calculated control on a report make sure that its name is not set to be the same as the field you are referring to. You mention [system], so make sure the calculated field's name is not also system. You will undoubtedly receive a #Error if you try to refer in a calculated control to a field name which is the same as the control itself - it's a circular reference. Name the calculated textbox something else such as TheSystem or whatever you think is appropriate, just make sure it is a different name from that of the field you are testing.

      In its control source property just enter =Nz([system]) to return a blank if the system field is null.

      If you want to return a more specific message, then change this to
      =Nz([system], "Field is null") or whatever suits.

      -Stewart

      Stewart,

      No names are the same but I did use your code but it still produces #Error. Can't figure it out. It works great if data is there with any of my codes as well as the one you gave me but with no data it continues to come up with #Error in the text box. Any other things that could cause this?

      David

      Comment

      • Stewart Ross
        Recognized Expert Moderator Specialist
        • Feb 2008
        • 2545

        #4
        Hi David. As NeoPa said in the other thread, there is something else amiss here. Nulls cannot of themselves cause #Error results in a report. There are some circumstances in which it can occur: when the field cannot be evaluated (as a result of a circular reference, or some other issue with the expression for that field), or when there is an illegal action such as division by zero taking place either directly within the expression for that item on the report, or indirectly within any of the queries feeding in to the report's source. Without knowing more about your report and the source query I would only be guessing if I tried to be more specific.

        -Stewart

        ps I see from the other thread that in fact it was an empty dataset problem - no records to report. From your attempts to test for nulls on individual fields it had seemed to me that you had #Error occurring on some, but not all, fields - how wrong to interpret it that way.

        I chose not to delete this thread when I noted the double-posting; I would advise that the thread should now be treatead as closed, however!
        Last edited by Stewart Ross; Mar 17 '08, 09:38 AM. Reason: added ps for closure

        Comment

        Working...