Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    report when item belongs to 2 groups (2000)

    Hi all,
    I would like to print a report that lists products by its "flag" value. The problem is that some products belong to 2 different groups and are identified with a 'B' in the flag control. If I put the grouping by flag in the report it prints 'B' then the products that belong to 'B' then the group letter from the frmInventFilter control and the products belonging to that group below that. If I take the grouping off the report then I don't know what dept printed out by looking at the pages. Is there a way to get the group letter to print out on the report , not print the group 'B' but add the 'B' products to the group where needed? If 'all' is typed that it prints the report grouped by flag and the 'B' products added to the 'f' and 's' group of products? Here is the code I am using to filter the report.
    Private Sub txtFilter_AfterUpdate()
    Dim stDocName As String
    Dim stFilter As String
    stDocName = "rptInvenItemsbyDept1"
    Select Case Me!txtFilter
    Case "a", "m", "x", "o"
    stFilter = "flag =" & Chr(34) & Me!txtFilter & Chr(34)
    Case "f", "s"
    stFilter = "flag = 'b' " & "Or " & "flag =" & Chr(34) & Me!txtFilter & Chr(34)
    Case "all"
    stFilter = ""
    End Select
    DoCmd.OpenReport stDocName, acPreview, , stFilter
    End Sub
    Thank you in advance.
    John

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

    Re: report when item belongs to 2 groups (2000)

    Could you try to explain that more clearly? I'm completely lost. It would also help if you attached a stripped down copy of the database. See <post#=401925>post 401925</post#> for instructions.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: report when item belongs to 2 groups (2000)

    Sorry for the confusion. Attached is the stripped down version. There are 2 reports, 1 where I added grouping, the other is not grouped. The form's after update event calls the first report with a filter.
    What I am looking for:
    If txtfilter = 'A' ,print products with a 'A' flag and print 'A' on report
    if txtfilter = 'F' ,print products with a 'F' or 'B' flag and print 'F' on the report; Now I get 1 page for 'B' and 1 page for 'F'
    if txtfilter = 'S', prints products with a 'S' or 'B' flag and prints'S' on the report; Now I get 1 page for 'B' and 1 page for 'S'
    Thank you for looking at my problem.
    John

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: report when item belongs to 2 groups (2000)

    Thank you Hans I should have thought that. This works great when 1 of the flag letters is entered.
    If 'all' is entered, is there a way of grouping the like flag item in the report and have the 'B' group show up in the 'F' and 'S' groups? I could send the print job 6 times with different filters in the case statement for 'all' but I thought maybe there is an easier way.
    Thank you again.
    John

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

    Re: report when item belongs to 2 groups (2000)

    Remove the grouping level. Instead, put a text box in the report header or page header with control source
    <code>
    =Forms!copyfrminventfilter1!txtFilter
    </code>
    This will display the text entered by the user in the text box on the form.

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

    Re: report when item belongs to 2 groups (2000)

    Do you mean that you want items with flag "B" to be displayed twice in the report?

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: report when item belongs to 2 groups (2000)

    When I put 'ALL' in the txtFilter control, I would like the items flagged with 'B' to appear with the items flagged as 'S' and have 'S' printed on first page of that group. The items flagged with 'B' should also appear with the items flagged as 'F' and have 'F' printed on the first page of that group. Groups flagged with 'A', 'M', 'O', or 'X' only have the items flagged with their letter in their group and their letter should appear on the first page.
    I can reply with an example if needed.
    Thank you.
    John

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

    Re: report when item belongs to 2 groups (2000)

    See the attached version. I created a union query to duplicate the records with flag "B" - each one appears once with flag "F" and once with flag "S":

    SELECT tblInventory.OorC, tblInventory.JN, tblInventory.OP, IIf([tblInventory].[Flag]="B","F",[tblInventory].[Flag]) AS Flag
    FROM tblInventory
    UNION SELECT tblInventory.OorC, tblInventory.JN, tblInventory.OP, IIf([tblInventory].[Flag]="B","S",[tblInventory].[Flag]) AS Flag
    FROM tblInventory;

    This query is used as record source of the report, which now can be grouped on Flag again. The code behind the form has become simpler:

    Private Sub txtFilter_AfterUpdate()
    Dim stFilter As String
    Select Case Me!txtFilter
    Case "all"
    stFilter = ""
    Case Else
    stFilter = "Flag =" & Chr(34) & Me!txtFilter & Chr(34)
    End Select
    DoCmd.OpenReport "rptInvenItemsbyDept", acViewPreview, , stFilter
    End Sub

    Because of the union query, we don't have to treat "F" and "S" separately any more.

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: report when item belongs to 2 groups (2000)

    This works great, very clever and elegant solution to my problem. It showed me things that I did not know were possible. Thank you very much.
    John

Posting Permissions

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