Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: filtered query's for reports HELP!

    Hi I got a database dropped in my lap not to long ago and so far I’ve managed to muddle though until the boss decided he wanted lots of new reports! Here is what I’m trying to do and I’m clueless!

    I have a column in a table called “container #” that I want to be able to filter and build a report based on the filter info. I was looking though the help file and it said something about filtered query I fallowed the link to another one of Microsoft’s grate dead ends with no info that was helpful.

    Any help would be most welcome!
    Thanks
    Rich

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    how does the report get launched?

    do you just want the user to be able to input a container # to base the report on?

  3. #3
    Join Date
    Jun 2004
    Posts
    9
    Yes that would be fine and probably the best way to do it vers coding a buton

  4. #4
    Join Date
    Jun 2004
    Location
    Beaverton, OR
    Posts
    4
    A filtered query is basically another query. If you want to ask the user upon opening of the report what container # they want to see, then in the query, in the column container #, where it says "criteria", type something like this: "[Enter the container #]". With the brackets in place, it will ask that exact question, and filter the report based upon what they enter.

  5. #5
    Join Date
    Jun 2004
    Posts
    9
    Ok got the query down. Now it ask what the user wants but when i enter a good field it comes back with empty fields is there some scripting i need to do to make it show up?

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    are you trying to create a user defined report layout on the fly or do you have a report set up that you want to filter based on the container # input by the user?

  7. #7
    Join Date
    Jun 2004
    Posts
    9
    The end goal of the report is to display all the items in 1 contaner in a printable form so that i can give this to people to verify the contents of a contaner. Then once i figure out how to do this i will then use the same report template and change it for each Department displaying multiple contaners in that report. So i need to be able to have the query that filters one contaner that OPS owns out of 17 in datablase. Then use this data to display in a report the contaners and the contents used in each. FYI I used a combo box for the data imput into my master table so in my master table i have multiple entrys for the contaner "OPS - 2"

    the first report i need to make should look like this (indicates a colum in master table) xxx is data CAPS LOCK is text in form used as headers
    CONTAINERxxx(container)
    {need to be able to filter for container i have over 125 of them}
    ITEM(item) OWNER(owner) SHOP(shop)
    xxxxx xxxxxx xxxxx
    xxxxx xxxxxx xxxxx
    hence the need to filter by container

    Then the second report i need to create looks like this

    DEPARTMENTxxx(department)
    CONTANER(contaner)xxx ITEM(item)xxx OWNER(owner)xx SHOP(shop)xx
    xxxxxx xxxxxx xxxxx xxxxxx
    xxxxxx xxxxxx xxxxx xxxxxx
    this form displays all the contaners owned by a department and may have multiple contners.

    I know that this should be simple stuff but i havent been able to find any thing in the help file. The Books i have dont tell me much eather. Thank you in avance for your time!

    Rich

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Create a query that shows the containers. Set the query's properties so that "Distinct" is true - this will give you a list of containers while removing duplicates.

    Create a form.

    On that form, place a combo box with a label that asks "What Container?". Skip the wizard, click in the combo box's properties.

    Make sure: RowSourceType property is Table/Query and set the Rowsource to the query you just created.

    Set the Limit To List property to Yes.

    In the After Update event property, select [Event Procedure] and then click the elipsis (the 3 dots) on the right side of that row.

    It will put you in the "Event Procedure" for that form, under something that probably will look like:
    Code:
    Private Sub Combo0_AfterUpdate()
    
    End Sub
    Put the following in that code section:
    Code:
    Private Sub Combo0_AfterUpdate()
    
         If Len(Me.Combo0.Value & "") = 0 Then Exit Sub
         DoCmd.OpenReport "ReportName", acViewNormal, , "[Container #] = " & Me.Combo0.Value
    
    End Sub
    *Poof* Instant "application"! When a user selects a Container Number, they get a report of just that container.


    Some notes:
    1. If the name (Combo1 in my example) is different, make sure the code matches.
    2. The command line shown will print the report to the default printer. Use acViewPreview instead of acViewNormal to open it in preview instead.
    3. My code assumes [Container #] is a numeric field. If it is text, use this line instead:
    Code:
    DoCmd.OpenReport "ReportName", acViewNormal, , "[Container #] = '" & Me.Combo0.Value & "'"
    The difference is the latter encloses the value in single quotes (otherwise you'll get an error to the effect of "Invalid data type")
    4. Obviously, use your report in place of "ReportName" (but keep the quotes)


    Good luck and have fun!

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    PS: Use a wizard to create the report for now, that will get you moving. The form I showed you above will handle the sorting for you.

  10. #10
    Join Date
    Jun 2004
    Posts
    9
    ok i was able to create a query and it showed all the items in the list however i was unable to figure out how to set the "Distinct" to a yes. Is that done via the sql edit mode in 2k? I tried "distinct"="yes" and "distinc"="true" in a simple query in critera box in the disign mode. i was able to get a full list of all the data in the field for that once i tryed to sort out the dups i'm not getin any data back? Thank you for your help and suport! Yes this is fun if not a little irksome at times. I think im goin to get a book on SQL Programing soon.
    Thanks
    Rich

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In the query design view, make the properties visible (icon has a hand holding a card).

    And sorry - it's called "Unique Values" in the query properties (Distinct is the SQL equivalent).

    I think it it's fun - especially since a lot of people can't do it!

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    oh, and get a book on Access first.

    Query building is tricky business as things get more complicated. Access make it easiy to build an effective query and as you learn to use Access, and start making more sophisticated queries, you can switch the query to SQL view and see the SQL underneath - this is a far better intro to SQL than a book.

  13. #13
    Join Date
    Jun 2004
    Posts
    9
    Thanks so much Worked Like a Charm!!!

Posting Permissions

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