Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    CALCULATED CONTROL HELP! (ACCESS 2000)

    IS THERE ANYONE WHO CAN HELP ME WITH THE FOLLOWING PROBLEM?
    I have an employee timesheet report that is grouped by Employee ID, Category & Project ID. I would like to create a calculated control that will add ONLY the first 2 categories together and give me a percentage of those categories against the total hours worked on the project by each employee. I have tried entering the following formula in the control source of a text box, but it always gives me the total hours of all the records in the database (It displays a sum equal to all the table's employee totals for the 2 categories combined):
    =DSum("[BillableHours]", "[TimeBilled]", "[Category] = 'Knowledge Development' And 'Administration'" And "[EmployeeID] = '???????'")
    My formula works fine in the control source of the text box until I try to add the employee ID (or employee last name) criteria part of the equation.
    WHAT CAN I INSERT THAT WILL ENABLE THE DATABASE TO DISPLAY EACH EMPLOYEE'S DATA BASED ON EITHER A EMPLOYEE ID OR EMPLOYEE LAST NAME????? PLEASE HELP ME!
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    You don't need to SHOUT, you know. Your messages will get looked at just as quickly without the caps. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I'm not entirely sure what your DSUM is supposed to be calculating. "[Category] = 'Knowledge Development' And 'Administration'" doesn't make sense unless Category contains two values in a single record. I can't tell what you're attempting with the EmployeeID argument, since you don't need it at all to return the total for all employees. If you want to total billablehours for all employees for Knowledge Development or Administration and those values are separate (i.e., some have Knowledge Development as a Category and some Administration), you would use either [Category]='Knowledge Development' or [Category]='Administration' or you could use [Category] In ('Knowledge Development', 'Administration') as follows:

    <pre>=DSum("[BillableHours]", "[TimeBilled]", "[Category] = 'Knowledge Development' or [Category] = 'Administration'")

    =DSum("[BillableHours]", "[TimeBilled]", "[Category] In ('Knowledge Development' , 'Administration')")</pre>


    Either of those should give you the total hours for those two categories for all employees, if that's what you're looking for. The percentage calculation for each employee, would require an additional calculation, but without knowing more about your form, it's a bit hard to tell you how to calculate it. To get the total of those two categories for an individual employee, you could always use this:

    <pre>=DSum("[BillableHours]", "[TimeBilled]", "[Category] In ('Knowledge Development' , 'Administration') And [EmployeeID='" _
    & [EmployeeID] & "'")</pre>


    Using DSums is slow, though, and you don't really want to keep recalculating the total for all employees, so you might want to populate an unbound textbox with the total value in the Open Event of the form and then refer to that control in your ultimate calculation.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    I am trying to sum 2 of 3 categories from the Category field (Knowledge Development & Administration).
    What I want my report to display is the sum of the [BillableHours] for EACH employee based on their employee ID.
    When I tried your suggestion for each employee I get a constant error message that states "The expression you entered contains invalid syntax. ....." The cursor returns to the underscore sign ( _ ) in the formula when I click OK to close the message box. I deleted the "_" and the formula works fine! Thank you very much!

    If the DSum function is slow how would I populate an unbound textbox with the total value on OpenEvent and how would this be referred to?
    Easy Access

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    What I meant is that the DSum shouldn't be recalculated with every pass through that routine. Instead, you can declare a module level variable in your report and populate it using a DSum in the Form's Open event. Then in the Detail_Format, or wherever you're running your code, refer to the variable in your calcuations instead of getting the total all over again each time. You only need a textbox if you have controls that have expressions in their controlsource, and you could set its value in the format event of the detail section by simply setting the textbox to the value of the DSum.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    Hey Charlotte,
    During the course of testing my database I discovered that my DSum control is only displaying the TOTAL number of hours for each employee no matter what date parameters I enter.
    I was having some problems with the " _ " in the formula you gave me to try. I continues to display an error message when I enter the "Employee ID portion" of the formula. I noticed you left off a closing bracket ( ] ). Was this on purpose?
    Right now what I have in my control is this:
    DSum("[BillableHours]","[Time Billed]","[Category] In ('Knowledge Development', 'Administration') And [EmployeeID]='" & [EmployeeID] & "'").
    Can you tell me what I need to include to get this to work, or even better a VBA code that will do the same thing?
    Easy Access

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    Your DSum is displaying exactly what it was told to display, the total for the employee. Domain aggregates like DSum operate on a domain, which in this case is the table or query [Time Billed]. They aren't affected by any filters you might apply to your form, so if you need a date parameter, you'll have to explain what you're trying to do so someone can help. If you want the value returned to be consistent with the filtered data in the form, you'll have to abandon DSum and do something else.

    If I left of a bracket, it was entirely by accident. I'm not sure why you're having a problem with the continuation character (space + underscore + enter), although it's possible that you don't have the space before it. I typed the formula in and that may have gotten lost in the typing.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CALCULATED CONTROL HELP! (ACCESS 2000)

    Could someone please help me with the following problem? I have attached a copy of my database for ease of explanation.

    I have a report (Employee Timesheets) that is grouped by Employee ID, Category and Project ID.

    I would like this report to display the following information in the Report Footer based on variable filters of:

    (a) Employee ID (Use: B1 or J1)
    ([img]/forums/images/smilies/cool.gif[/img] Date parameters: (Any range of dates from 5/18/02 to 8/8/02)

    1. Sum total of Category
    Attached Files Attached Files
    Easy Access

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •