Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105

    IIf statement

    Yes, and horribly cumbersome, sorry. I am rather a novice here. With some help I got this going.
    I deleted some and hid some(of the queries.) The reports are all in the format required by the funding agency. For the Summary table of consumption by budget heading, the underlaying query RptSUMMARY table of budget consumption by revised budget gives the detail, and I sum in the report by Accounting heading and Main budget heading, moving the totals to the top instead of the normal footer section . To
    On the switch board, Click on Open Reports.


    Type in a start date of eg 1/7/15 end today's date
    to get an example of the report.
    Regards

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks the same as the last one - the autoexec is still there generating errors with about 35 reports and 40+ queries. None of which are called what you say they are called.

    I would expect one or two forms, perhaps a couple of queries and one or two tables

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Finally found the way through

    you need to refer to the controls, not the underlying data

    =IIf([text187]=0,0,[text186]/[text187])

  4. #19
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Not sure I understand. I have tried to show only the tables, queries involved in the report. For your further advice please.

    These are the two reports that have the #NUM problem.

    Thanks and regards

  5. #20
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Ok If I understand you are saying the control source for the box in the properties. =[AmountSpent]/[BudgetAmount] In this example, the items are what is in the control source on the property sheet

  6. #21
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ok If I understand you are saying the control source for the box in the properties
    you need to refer to the control itself and not its controlsource - since you have not given your controls any meaningful names, the code I posted is the code you need - at least for the report I looked at.

  7. #22
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Ok, I get what you are saying now. I tired the code, but it still does not work for me. If you dont mind, put the code in and send the db back to me.
    Much appreciated.

    Regards

  8. #23
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    fixed it in one of the reports - strongly recommend you adopt a proper naming convention, it is a nightmare to navigate

  9. #24
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Hi,
    The new formula fixed the ones that should be "zero" showing as zero eg 1.3, but the other ones accounting headings, the calculation is incorrect. Eg 1.1 total numbber of records 43,981.81 as a percentage of 84,364.46 gives a percentage of 191.8 %.
    Any suggestions?

  10. #25
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so what should it be? - remember, basic maths

  11. #26
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    My apologies and thanks. I copied your formula wrong. I switched and the answer is CORRECT.

    So appreciative.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  2. Need help with a Like statement
    By itm in forum Queries
    Replies: 3
    Last Post: 10-22-2012, 12:55 PM
  3. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  4. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  5. If Statement Help
    By vennies83 in forum Queries
    Replies: 1
    Last Post: 06-09-2011, 03:50 PM

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