Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    8

    Unanswered: Report and sub report problem

    Hello all you lovely people.

    I have one main report.
    Inside that report I have about 7 subreports. All doing their own thing from their own queries. None of these sub report queries are by date range at the current point in time. They all query by a 'group' field. I have used all the subreports because I want different values for some than others and also I want to also calculate my own totals from those reports in other places.

    This main report and all of the subreports work 100% and give me the correct answers and layout I want, so long as they are using ALL the data in the underlying tables etc.

    However, Is there a way to code the MAIN FORM so that when it is opened, the user is asked for a 'Start Date' and a 'Finish Date' and those two dates will then become an additional criteria for each subreport that needs data by date. I assume that if I was to give each subform criteria by date then a user will need to enter it 7 times!!!!!!. Am I correct? Or, am I trying to do this job totally the wrong way.

    Can someone please give me some guidance or assistance on this problem.

    Thanks so much
    Daisy

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Depending on the specifics of your application, options include:

    1) use a form for the user to enter the criteria, and have all the report/subreports look there for them.

    2) have the main report get the criteria and let the master/child links between the report/subreports keep them in sync.
    Paul

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I have done something similar in forms, where the main form had a series of combo boxes which provided parameters. The source for each of the subforms was a query which referenced the appropriate combo boxes in the main form. You can use the Query Builder or straight SQL .

    The clause comes out looking like:

    WHERE DepartmentKey = Forms!frmCompany!cmbDepartmentKey

    I don't see why something similar should not work in reports. You could actually create a form which accepts the parameters and which contains a button to launch the report. So long as the form remains open, the full object name for the combo box will provide an acceptable reference n the subreport query.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ ... in other words
    1) use a form for the user to enter the criteria, and have all the report/subreports look there for them.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2008
    Posts
    8
    Thanks very much guys. I have tried what you suggested (coz you are experts...lol) but still missing the obvious I guess.

    I created a form from a query.....and yes the info on that form is A OK and yes, I can select the data by date as I want. I tried giving the subreport the source of the form but then it started to ask for the date range again so I took it out.

    I will give made up examples of four rows of that form.

    4000 Bottles Direct Income $4000.00
    4100 Rags Direct Income $200.00
    6000 Wages Direct EXPENSE $1000.00
    6100 Power Direct EXPENSE $100.00

    I am trying o hook the subreport to the form but I ALWAYS only ever get ONE of the above lines. It seems to be only showing the data line which is highlighted by the cursor.

    What I really am trying to do is shift ALL the Direct Income to one subreport - line by line exactly as above - and ALL the Direct Expenses to another subreport line by line. On some occassions there might only be one to shift or there might be 10 to shift so I cannot really just do them line by line because every user will be different.

    I am using the following code right now which only will shift one row at a time.

    =Forms.ProfitDateSelectForm.Form![Account Number]

    Can someone please show me my error in the above code.

    Thanks very much again
    Daisy

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you use that code in an expression, you'll get only that data which has that specific account number.

    What you should do is have one form with your date criteria on it and refer to that with your subforms.

    Imagine a form called dlgDateSelect. It has dtStart and dtEnd as objects on it that hold the start date and end date for the date range.

    In the SQL for the forms/reports, you set a criteria for your date field:

    FROM ... etc
    WHERE YourDateField Between forms!dlgDateSelect!dtStart And forms!dlgDateSelect!dtEnd

    That will give your forms/reports all the data between the two dates entered on the form.

    Hope that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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