Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    5

    Unanswered: exporting calculated fields on a report to excel

    I have created a report in access which has the hrs an employee has worked on a task....I have totalled these hrs for that task by using:

    Total Client Hrs:

    =Int(Sum(DateDiff("n",[startTime],[endtime])/60)) & ":" & Format(Sum(DateDiff("n",[startTime],[endtime])) Mod 60,"00")

    When I go to export this report into excel, the calculated field does not appear....why is this?

    Would anyone know how I can show the calculated field in excel?

    I have not had much experience exporting reports in access to excel..

    Thxs

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Listen for the somber funeral music ...

    When using the standard exporting options for a report from Access 97 (it may be different for newer versions) the calculated fields do not go, as you have discovered (the group sum fields don't work right either - they sum the entire column, not just the group).

    Easiest fix: perform the calculation in the query.

    In the query, make add a field similar to the example I cut and pasted here from your message, and in the report, make the new field the record source for your control (instead of the function):

    CalcValue: Int(Sum(DateDiff("n",[startTime],[endtime])/60)) & ":" & Format(Sum(DateDiff("n",[startTime],[endtime])) Mod 60,"00")

    I try to avoid using equations in a query due to performance and debugging issues, but it's a direct fix. There is another thread about using a user defined function in the qury and placing the function in a module if you're interested.

    Have fun!

  3. #3
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    5
    Ok...so I have put the calculation in the query but when I go to run the query it shows this error message:

    Total Client Hrs: Int(Sum(DateDiff("n",[startTime],[endtime])/60)) & ":" & Format(Sum(DateDiff("n",[startTime],[endtime])) Mod 60,"00")

    "You tried to execute a query that does not include the specified expression "EmployeeID" as part of an aggregate function."

    because my query has criteria that an employee is selected from a combobox and a first and second date for the period of the work done.

    what is the solution for this....???




    Originally posted by tcace
    Listen for the somber funeral music ...

    When using the standard exporting options for a report from Access 97 (it may be different for newer versions) the calculated fields do not go, as you have discovered (the group sum fields don't work right either - they sum the entire column, not just the group).

    Easiest fix: perform the calculation in the query.

    In the query, make add a field similar to the example I cut and pasted here from your message, and in the report, make the new field the record source for your control (instead of the function):

    CalcValue: Int(Sum(DateDiff("n",[startTime],[endtime])/60)) & ":" & Format(Sum(DateDiff("n",[startTime],[endtime])) Mod 60,"00")

    I try to avoid using equations in a query due to performance and debugging issues, but it's a direct fix. There is another thread about using a user defined function in the qury and placing the function in a module if you're interested.

    Have fun!

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sorry, I should have asked if it was a "totals" query.

    Revert back to your previous query.

    Then, create a new query, add your query above and add the extra field.

    Use the new query in your report. Access doesn't like a lot of shinnanigans it's "totals" queries - so you have to do a subsequent query to add the calculation.

    If you aren't using a "totals" query, then something else changed; revert and try using a subsequent query as described above.

    Also, to prevent problems in other areas down the road, may I suggest you remove the spaces from your field name?

    Hope this helps!

Posting Permissions

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