Results 1 to 8 of 8
  1. #1
    patheo is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    3

    #error in form field returning report's value

    Hi all!

    I'm using Access 2003 and I have a form that returns a value from a report with the following formula =Reports!CA_PB!Text64
    In my report, I have the following formula =IIf(Report.HasData;Sum(([INDEX_fin]-[INDEX_DEP]-[RC])*[POD_TTC]);0) to avoid error in my form when the related query has no data.
    My problem is that when the report's field value is "0", my form returns #error



    Please, could you help me please?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is it just one field that has a value of "0"? Is that a text field? Is the "field" in the RecordSource of the Report or in a Control *on* the Report?

  3. #3
    patheo is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    3

    #error in form field returning report's value

    Quote Originally Posted by RuralGuy View Post
    Is it just one field that has a value of "0"? Is that a text field? Is the "field" in the RecordSource of the Report or in a Control *on* the Report?
    Is it just one field that has a value of "0"? YES
    Is that a text field? NO it is a currency field
    Is the "field" in the RecordSource of the Report or in a Control *on* the Report? YES

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So in the Open Event of the form you could test that field and close the report if zero value, right?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you sure it has a value of 0, or perhaps are there no records? If the latter:

    nnz function
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Is the report correctly displaying the "0" in [text64] when there is no data? Did you try running the report in preview mode when there will be no data?

  7. #7
    patheo is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    3

    #error in form field returning report's value

    Quote Originally Posted by John_G View Post
    Is the report correctly displaying the "0" in [text64] when there is no data? Did you try running the report in preview mode when there will be no data?
    Yes, the report is correctly displaying "0" in [text64] when there is no data. I tried it and get #error on my form every time there is no data.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Interesting. A little experimenting has shown me that if the report has no data, your expression Reports!CA_PB!Text64 returns no value.

    I ran a test report twice, once with data, once without, and with a text box is the report header stating "Has Data" or "No Data" as applicable. I found that if the report had data (and was open) I could see the data in the report header text box with ?reports![golf scores].[text43] in the immediate window (Ctrl-G), but if the report had no data, I got error 2427, "You entered an expression that has no value", even though the report header was visible with the proper message.

    So that's the reason for your #error value. What you could do is test if the report will have any records before you open it (a DCount() on the report's query might work, with the same criteria as for the report), and then only open the report if there is data for it.

    An interesting problem - we learn something new every day.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 08-28-2015, 07:43 PM
  2. VBA Code Returning Error Run Time Error 3061
    By tgwacker in forum Access
    Replies: 2
    Last Post: 11-24-2013, 11:00 AM
  3. Replies: 8
    Last Post: 04-29-2013, 12:11 PM
  4. Creating Buttons on a form keeps returning a visual basic error
    By Robin Banks in forum Database Design
    Replies: 1
    Last Post: 02-28-2012, 07:48 AM
  5. Blank Field returning #Error
    By imintrouble in forum Access
    Replies: 8
    Last Post: 12-09-2011, 03:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums