Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from a subreport (2002 - SP2)

    I have a 4 text boxes, that individually apply filters to a subreports data.

    I have a print button, linked to another report, which is linked to a query. (essentially printable labels)

    I want to send the data from the filtered subreport, to the query/report.

    I have tried pointing the query to the subreports ID field, but that doesn't seem to work.


    Is there a way of sending the subreports data, into a query/report. OR

    do i have to make the query examine the main forms Text boxes, and perform the 'filter' again in the query, before it gets sent to the report. ?

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

    Re: Query from a subreport (2002 - SP2)

    > Is there a way of sending the subreports data, into a query/report.
    No, you can't refer to data in a (sub)report. A report is a way of viewing/printing records only.

    > do i have to make the query examine the main forms Text boxes, and perform the 'filter' again in the query, before it gets sent to the report. ?
    Yes, that's the way to do it.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a subreport (2002 - SP2)

    10 minutes Hans... Cheers. !

    Ps: Where you want this beer shipped to ?

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

    Re: Query from a subreport (2002 - SP2)

    Upload it through BTP (Beer Transfer Protocol). I'll tell my firewall to leave the Beer Port wide open.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a subreport (2002 - SP2)

    Ok hans will do.... (prefer the stuff thats comes in crates myself)

    One problem i have run into, if you happen to read this... is the search in the query produces strange results.

    There are 2 text boxes, and 2 combo boxes. (that filters the data in the subform)

    In the query thats going to output the filtered data into the form, i am using

    Like "*" & [Forms]![Main Form]![Search_Box1] & "*"

    So i'm using that for the Criteria in the query. For the Combo's it will just use the exact text.


    However, im getting strange results when i try to use more than 1 of the text/combo' boxes in the query.

    What i *think* is happening, is that the LIKE "*" will pull up everything, if the box is blank. (which is always the case, because im only using 1 of the 4 boxes each time to filter)

    I've tries using Is Null, and = "", in the remaining boxes, but i dont think thats the problem.

    The query needs to be able to take any partial input from Search boxes 1 and 2, or, any fixed value from combo's 1 and 2.

    Any ideas ?

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

    Re: Query from a subreport (2002 - SP2)

    Can you post the SQL of your query, and/or a screenshot of the query in design view (showing the criteria)

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a subreport (2002 - SP2)

    This, as is, works fine... but as soon as i add another LIKE statement, it will not work correctly, and then i need to add a test for the other combo boxes,and compare those against the query.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a subreport (2002 - SP2)

    Overly large screenshot cropped by HansV

    Hans, here is a screenie. Search_Box and Search2 are the text boxes where i have to use part of the box, so hence have to use LIKE in the query.
    Combo123 and Combo125 are the Combo's

    Search_Box works on Document Name
    Search2 works on Document Reference Number
    combo123 works on Document Location
    combo125 works on Document Owner

    If that makes sense...!

    As is.. the SQL is :-

    <hr>SELECT [External Document Registar].[External Documents ID], [External Document Registar].[Document Name], [External Document Registar].[Document Reference Number], [External Document Registar].[Document Location], [External Document Registar].[Document Owner], [External Document Registar].[Date of Receipt]
    FROM [External Document Registar]
    WHERE ((([External Document Registar].[Document Name]) Like "*" & [Forms]![Document Library]![Search_Box] & "*"));<hr>

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

    Re: Query from a subreport (2002 - SP2)

    How are you adding the other conditions? In the same row or in different rows?

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a subreport (2002 - SP2)

    I've tried both.. neither way works correctly.

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

    Re: Query from a subreport (2002 - SP2)

    I don't think we can solve this without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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