Results 1 to 5 of 5
  1. #1
    ByronC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    2

    Problems with subreports and IIF statments

    Hello,


    I've been using access for many, many years, but I've run into a problem with subreports. The issue is that my IIF statement works intermittently. The first screen shot is of the report working. After saving the report, closing the DB, then re-opening, the #Name? error comes back (2nd screen shot below).

    "Budget Projection" and "Payroll and Fringe Estimate Through Budget End Date" are both subreports on a report named "Budget Status." These reports pull criteria from a form [frmReportMenu]. I'm pulling [txtPayrollEstimate] from the report footer of the second sbrpt and using it in a calculation on sbrpt "Budget Projection."

    BUT, in order to troubleshoot the cause of #Name? error, I simplified my IIF statement to: =IIf([Account]="SAL_SP",1,0)
    This simplified IIF is producing the same error. So, IIF is intermittently not recognizing [Account] (second column below). I used the expression builder and it has no problem with [Account] and I have no duplicates of Account either. Account is the text box name and the control source from the query that feeds the sbrpt "Budget Projection."

    I'm sure there are multiples ways to do what I'm doing, but my VBA skills are very limited. In other works, I avoid it at all costs.
    Thank you in advance for any help.
    ByronC

    Click image for larger version. 

Name:	Budget Status Report without error.JPG 
Views:	13 
Size:	100.1 KB 
ID:	26743
    After saving, closing and re-opening, the error returns (when I took this screen shot the Account label was "Category" but I changed it back to "Account" for this post.
    Click image for larger version. 

Name:	Budget Status Report with Name error.JPG 
Views:	13 
Size:	99.6 KB 
ID:	26744

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I would recommend doing all your values and calcs in the record sources. There is no need to do them inside the report - far easier to troubleshoot a query. Get the query working, the report is only there to display the query values in a nice manner.

  3. #3
    ByronC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    2

    Conditional calculations

    Quote Originally Posted by aytee111 View Post
    I would recommend doing all your values and calcs in the record sources. There is no need to do them inside the report - far easier to troubleshoot a query. Get the query working, the report is only there to display the query values in a nice manner.
    Aytee,
    Thanks for the push in the right direction. It was much easier than I thought! Funny how this works in a query 100%, but only 50% in a report.

    =IIf([Account]="SAL_SP",[txtPayrollEstimate],IIf([Account]="FRG_SP",[txtFringeEstimate]),IIf([Account]="FACADM",[FA_Est],0)))

    ByronC

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Glad to be of service!

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    nm, looks like you have it.

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

Similar Threads

  1. randomizing statments on report
    By BatmanMR287 in forum Reports
    Replies: 2
    Last Post: 07-12-2015, 01:34 PM
  2. If statments??!!
    By wmagargle in forum Programming
    Replies: 4
    Last Post: 08-30-2012, 02:45 PM
  3. Problems wich subreports
    By fabiobarreto10 in forum Reports
    Replies: 1
    Last Post: 08-07-2012, 05:12 AM
  4. subreports
    By emerrube in forum Access
    Replies: 2
    Last Post: 08-24-2011, 07:35 AM
  5. Using Subreports
    By jonesy29847 in forum Access
    Replies: 5
    Last Post: 04-20-2010, 11:16 AM

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