Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12

    Exclamation Pulling Textbox Value in Control Source of Another Textbox

    Hi,

    I am currently in the process of compiling a new access database to calulate outbound abandon rates for an outbound dialler with Raw Data exported directly from a central database, not through the provided software from the dialler (which is hurrendously resouce intensive on the client and server end machines)

    My current control source for a textbox:


    Code:
    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))/([Third Party Contacts]+[Right Party Contacts]))
    What i want to do is input the value of a text box called "New_Abandoned" into the latter part of the Control Source code as such:

    Code:
    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))/([Third Party Contacts]+[Right Party Contacts]+[New_Abandoned]))
    This issue i get with this is that it returns:
    The Microsoft Office Access database engine does not recognise '[New_Abandoned]' as a valid field name or expression.
    I have also attempted to add back in the formula which is within the textbox into the control source as:

    Code:
    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))/([Third Party Contacts]+[Right Party Contacts]+([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))))
    But the Field then just returns as '#Error'

    Please can someone help with the correct method for pulling the value of another textbox in a Control Source method?

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The control source gets its data from your form's record source, so New Abandoned does not exist in the record source. Either use "Forms!FormName!NewAbandoned" or in the AfterUpdate even of NewAbandoned calculate the value.

  3. #3
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    The Textbox does not exisit on a Form, it is a textbox within the "Footer" section of a Report, will i still be able to refer to it using:

    Reports!ReportName!New_Abandoned

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sure - if the report is open. It might be safer to recreate the value instead of getting from the report, however.

    Is the textbox you are trying to create also on the same form? Then definitely recalculate it using data from your tables/queries.

  5. #5
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    The above didnt work, it gave me the same message.

    i went back to some of my old Excel formulas to see if there was a layout problem and i spotted a ) in the wrong place:

    The code i tried:
    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))/([Third Party Contacts]+[Right Party Contacts]+([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))))

    The code that works: Note the first set of )))) now has a 5th ) and one less at the end. This has corrected the issue and no needs to create additional queries/tables to calulate and report back to the report.

    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))))/([Third Party Contacts]+[Right Party Contacts]+([Abandoned Calls]-Sum([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))))

    Much appreciated for you help, may need you again soon, Excel is my baby and im still learning access.

    Regards,
    D

  6. #6
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    Hi,

    This is now working for the Groups however not working for the grand total when i use the same formula, any idea why this wouldnt work as it is all the same data and field names?

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Must be something to do with the report layout - is the grand total on the report footer?

  8. #8
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    Yes, the other fields sum correctly and matches the rest of the data however when i apply the same formula into the field in the Report Footer, Nothing shows in the box.

    Any ideas?

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not a clue! Would you like to send me your database?

  10. #10
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    Hi,

    problem i have is that the Database is ODBC Linked Table and you wouldnt be able to pull the data?

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query that contains the data you are trying to report on (doesn't matter if it isn't absolutely accurate), make sure it contains numerical data.
    Then go to the Report Wizard and create a report, summing on one of the fields.
    When the report has been created, have a look at how and where the wizard has created the report grand total. Have a look at its properties, location, control source, etc.
    See if you can spot the difference between your report and the one Access created.

  12. #12
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    I have attached the report and some dummy data in a table and changed the Control Sources to read from this for you.

    Any help you can give me would be greatly appreciated.

  13. #13
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    Quote Originally Posted by aytee111 View Post
    Create a query that contains the data you are trying to report on (doesn't matter if it isn't absolutely accurate), make sure it contains numerical data.
    Then go to the Report Wizard and create a report, summing on one of the fields.
    When the report has been created, have a look at how and where the wizard has created the report grand total. Have a look at its properties, location, control source, etc.
    See if you can spot the difference between your report and the one Access created.

    The grand total fields work before i start making the formula change however there is a lot of complexities in the formula like calulating the percentage of answer machines against the total connects. Minus this percentage off the total number of dropped calls.

    Once done, then divide the remainder of the dropped calls by the total number of Right Party Contacts, Third Party Contacts and again the remainder of the dropped calls as per the above:

    Example:
    Right Party = 100
    Third Party = 100
    Answer Machines = 1000
    Dropped Calls = 50

    Answer Machine percentage = 83%

    50-83% = 8.5 dropped calls remaining.

    8.5/SUM(100+100+8.5) = 4.07%

  14. #14
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    =[Abandoned Calls]-Sum([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))

    This is the value on your Description Footer - it doesn't make sense. What is the of the fields? Access has no data at this point, and if it is using anything it is using the last processed value - and who knows what that is. I suggest you open up your whole report so as to see the details and work out if your logic is correct.

    Another thing I would suggest, whenever you create a report or form that is for display purposes only get as much as you can done in the query, it is a lot easier to troubleshoot than a report. In this case, create a Totals query and get your figures right (click on the Greek "E" in query design) prior to using them in the report. That is a better way of doing things than making the detail area invisible.

  15. #15
    DlrMngr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    12
    Never mind, got it

    This is the one working for the group:
    =Sum(([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))))/([Third Party Contacts]+[Right Party Contacts]+([Abandoned Calls]-Sum([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))))

    =Sum([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine]))))/(Sum([Right Party Contacts]+[Third Party Contacts])+Sum([Abandoned Calls]-([Abandoned Calls]*([Answering Machine]/([Third Party Contacts]+[Right Party Contacts]+[Answering Machine])))))


    ive never known such an cruddy system for Sum formulas in all my life.

    Much appreciated for your help.

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

Similar Threads

  1. Replies: 16
    Last Post: 01-16-2012, 09:43 AM
  2. Replies: 5
    Last Post: 10-13-2011, 03:36 PM
  3. TextBox control not linking to query data
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 09-28-2011, 09:56 AM
  4. Load textbox from another textbox
    By siddel77 in forum Programming
    Replies: 12
    Last Post: 08-30-2011, 01:46 PM
  5. Textbox Control Source As An Expression help!
    By emilyrogers in forum Forms
    Replies: 11
    Last Post: 02-11-2011, 07:31 AM

Posting Permissions

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