Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSum (Access 2000)

    Thanks for the help I have received. I have got Access to do nearly what I want. I have one more question if anybody can help. I have a report called Invocice which includes a Subreport this has Qnty,item cost and Freight ,information put in by Technicians.This used further down the report to calculate the total parts used and a total for Freight and an Invoice is printed,a relationship exists between Job Numbers so all the information is linked to a job. The problem I have is when more than one record for parts etc exists in the subreport the calculation I use in a text box(named total parts used) only works on the first record and gives a total for one record even though 5 different parts were used for a job (the five records show in the subreport)I have tried using DSum to calculate the total costs for parts etc but this only seems to work with Tables and gives a Total for all parts used irrespective of Job Numbers. What I need to do is have an Invoice which shows a Total parts cost and Freight cost for a particular job can anybody offer a suggestion?

  2. #2
    New Lounger
    Join Date
    Nov 2001
    Location
    Hokitika, West Coast, New Zealand
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (Access 2000)

    In the footer of the subreport create a text box to sum the totals you need. When the subreport is linked to the parent report by a job number, only the sum of the items in that job number will be included. The subreport footer will not be visible in the parent report but you can use a text box in the parent report to collect the value of the total on the subreport.

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (Access 2000)

    Thanks for the help,I must need my hand holding because I cannot get your suggestion to work.The expression code I put in Text box 24 in the subreport footer is =DSUM("[Total cost:]","Cost of parts") which like you say is hidden, in the main report Text box I put ={Reports]!{Invoice]![Cost of part subreport1]![Text24] but I get #ERROR if I use this expression.I have tried several expressions but without any success, what am I doing wrong?

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

    Re: DSum (Access 2000)

    You're trying to refer to the subreport as if it were an open report, and that's not the way you refer to a subreport. From the parent form, you would refer to it like this:

    =Me!<SubReportControl>![Text24]

    Where <Subreportcontrol> should be replaced with the actual name of the subreport control on your report.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Hokitika, West Coast, New Zealand
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (Access 2000)

    Try this:

    Forget DSum - I just use Sum

    In the subreport footer, set the control source for Text24 to read =sum(cost of parts) - I am assuming 'cost of parts' is the field you are trying to add.

    In the main report, in whatever text box you are using set the Control Source to read =[costofpartsubreport].Report!Text24

    It works that way in more than one of my databases.

    I must say I haven't yer had cause to use DSum so know little about using that.

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (Access 2000)

    Thanks for your suggestion Charlotte, what should I put in the Text box I created in my sureport I have tried DSUM to total the Total parts column but this didn't work as I expected.

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Hokitika, West Coast, New Zealand
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum (Access 2000)

    Graham, do you have the Northwind sample database on your machine? If so, have a look at the Orders form and the Order Details subform. There you will see an example of what you are trying to do.

    MS used to also have a Solutions database which was shipped with Access and had many examples of useful code and helpful hints. I am not sure if they still use it.

Posting Permissions

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