Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parent-child links (2000)

    I want to place 2 or more crosstab tables, made into reports, on one master report. I am using QryA to build CrosstabA, and CrosstabB and CrosstabC, so the data is from the same pot. I am limiting it by a date field, and putting the [parameters] in each crosstab query's Parameter fields. But when I run the master report, I am forced to enter the date information multiple times (6 times if there are 3 crosstabs, 8 if there are 4).

    The parent-child link doesn't work because the date field is not one of the crosstab columns/rows, although the crosstab uses the date criteria entered when it runs.

    Is there any way to make the report run entering the date range only once? Is there a way to include the date range in the crosstab so it could be used for parent-child? Or am I going about this all wrong?!

    Thanks for your help on this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent-child links (2000)

    You might create a small form that allows the user to enter the parameters. Instead of [Enter value here], you can use [Forms]![frmParameters]![txtValueInput] as parameter in the query. Perhaps you can also put the link with the main report into the queries as criteria [Reports]![rptMainReport]![txtSomething].

    If this doesn't help, please provide some more detail, perhaps with the SQL of your queries, or a screenshot.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent-child links (2000)

    I'm sorry. I am not familiar with using a form or report as part of a query. Do you have an example I could look at?
    I'm attaching a couple of the SQLs, and an example of what the end product looks like. Thank you for helping on this- it's an ongoing issue since our data is so date-dependant and the reports we run tend to use date as the only criteria.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent-child links (2000)

    I don't know what GeneralQry looks like, but you could put criteria like this in GeneralQry

    WHERE SomeDate Between [Forms]![frmParameters]![txtStartDate] And [Forms]![frmParameters]![txtStartDate]

    where SomeDate is the name of your date field and frmParameters is the form used to input the start and end date into text boxes txtStartDate and txtEndDate (they must be set to some date format). Specify [Forms]![frmParameters]![txtStartDate] as a Date/Time parameter, and [Forms]![frmParameters]![txtStartDate] as well, in GeneralQry and in each crosstab based on it.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent-child links (2000)

    Forgive my ignorance, but how does making a form to enter the date range criteria, rather than using criteria of 'Between [date1] And [date2]' for that field, enable me to have a date field in the crosstab? I 've never gotten crosstabs to allow fields that don't have a criteria in them, so I don't see how I can put the parameter in the crosstab regardless of whether it uses a form or some other means - or am I missing something?

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

    Re: Parent-child links (2000)

    In the design view of the query, select Parameters from the query menu. Then enter the parameter there. For instance, if you want to use a date that was input into a form, and you want the criteria for a date field to be something like <= forms!MyForm!DateValue then you would put [forms]![MyForm]![DateValue] in the parameters as a DateTime value.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent-child links (2000)

    I have attached a demo database (Access 97, zipped). The data in the table and the report are nonsensical, but it shows how to use a form to enter date criteria. If you open the queries in design view and look at the parameters, you will see how they refer to text boxes on the form.
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent-child links (2000)

    Thank you so much. I'm not sure why that works and the other method does not, but I certainly can work this.
    However, I was also using my [start date] and [end date] in the report headers.... = " For Events Reported Between "&[start date]&" and "&[end date]. I've tried substituting the [Forms]! statement but that doesn't seem to work. I can enter the dates twice, but if possible would prefer only one entry. Do you have any thoughts/ suggestions?
    Again, I really do appreciate the help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent-child links (2000)

    Hi Judy, if I put a text box in the report header with Control Source

    ="For Events Reported Between " & [Forms]![frmParameters]![txtStartDate] & " and " & [Forms]![frmParameters]![txtEndDate]

    it works as intended, without asking for the dates. Note that the reference to the text boxes on the form has exactly the same form as the parameters in the two crosstab queries. (Defining the parameters explicitly in the crosstab queries is the key to get this working)

  10. #10
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent-child links (2000)

    I tried it again, and while it doesn't ask for the second set of dates, it also doesn't display correctly. For me, it displays on the report as Name?
    I know my syntax is correct - can you see anything wrong?
    ="For Medication Events occurring between " & [Forms]![frmDates]![txtStartDate] & " and " & [Forms]![frmDates]![txtEndDate]

    I thank you for your continued support on this.

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

    Re: Parent-child links (2000)

    The #Name? message indicates that something is misspelled or that what you typed in doesn't match a control on the form. It is the equivalent of getting parameter popup when you run a query. Make sure you actually have controls on the form called txtStartDate and txtEndDate or else replace those references on the report with the actual names of the controls on your form. And of course make sure that the form name referenced is the actual name of the form you have open.
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent-child links (2000)

    Hello Judy,

    As Charlotte wrote, #Name usually means that something is misspelled. As a test, simplify the expression temporarily. First, try

    =[Forms]![frmDates]![txtStartDate]

    If this also results in #Name, there must be something wrong with the names - either the form is not named frmDates, or the text box is not named txtStartDate, or frmDates is not open.
    If it works OK, repeat with txtEndDate instead of txtStartDate.

Posting Permissions

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