Report Query with select distinct question

[Micron]

You're saying you realize you have distinct records in that query and that they are caused by fields you're not even using in the report (and there are many of those)? So the obvious solution is to remove the table for those fields (since the fields causing this are all in the same table), no? I don't see any other way that would make more sense based on what you have been able to show us. By that I mean if this query is used elsewhere, then you need to customize it for the particular purpose.

[d9pierce1]

Thanks Micron,
The problem is not with removing the fielsds and tables from the query, to use my report filter it needs those fields to generate the report and I dont know how to reference the report lstboxes on the report form?
Otherwise, I get a parameter value error?

If you take out that table and the related fields that is OK, however, if you then use the report filter, you get a parameter error. I tried to put txtboxes on report form to relate to the lstboxes but I dont know the correct method of getting to the lstbox like I showed in a previous post

Thanks
Dave

[madpiet]

You don't reference the listboxes etc from the report really. If you're using the listboxes etc to select values to include in the report, you use VBA in the button or whatever to open the report and process the list of items there by creating a filter. Something like (1) create a variable to hold the list of selected items, then (2) loop through .ItemsSelected collection of the listbox and append them to the string. variable in step 1. (and wrap values in text or date delimiters as appropriate) and then crank out a comma separated list maybe. So you end up with a filter like

[ColumnX] IN ('val1', 'val2', 'val3])

and then you pass that filter in the On Open event of the report. (because it filters the dataset the report is based on).

[d9pierce1]

Thank you and that sounds like a great thing to do, however, it does go way above my knowledge of actually creating that...
I am just not real good at understanding vba at that level.
Dave

[d9pierce1]

Hi again Madpiet,
I think I all ready have that in my button code, see below!
However, the report is searching for the ID's and gives a parameter error if I select anything?
Suggestions?

Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc As String


If IsNull(Me.LstBusinessReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


On Error GoTo Err_Handler


   With Me.LstBusinessReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With
If Not IsNull(Me.LstIndustry.Column(0)) And IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstFunction.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[BusinessPrimaryIndustryID] in(" & getLBX(Me.LstIndustry) & ")"
ElseIf Not IsNull(Me.LstIndustry.Column(0)) And Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstFunction.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[BusinessPrimaryRoleID] in(" & getLBX(Me.LstCategory) & ")"
ElseIf Not IsNull(Me.LstIndustry.Column(0)) And Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstFunction.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[BusinessPrimaryFunctionID] in(" & getLBX(Me.LstFunction) & ")"
Else
    'DoCmd.OpenReport strDoc, acViewPreview
    
    If Forms!frmBusiness.sfrmBusinessContact.Form.chkSingle = True Then
        'MsgBox ("Hello")
        DoCmd.OpenReport strDoc, acViewPreview, , "BusinessID=" & Forms!frmBusiness!BusinessID
    Else
        DoCmd.OpenReport strDoc, acViewPreview
End If
End If


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
 
End Sub

[Micron]

I just am not seeing the need for a filter if the query returns what you need for the report. If you need a filter for some reason, obviously the field you filter on has to be part of the recordsource, but if you're not showing that/those field(s) anyway then why? The answer could be that your schema is not correct. While that's my impression based on your reply and the myriad of tables that look questionable I'm not going to wade through it all to prove that one way or the other. I did as I suggested and got one record, which is what I thought you wanted and the displayed fields are exactly the same. If that's not a solution for you then I don't think I know what that will be.

[d9pierce1]

The whole purpose of the filter is that I have over 20,000 business in my db. Some are construction, some are doctors, medical, some are suppliers and what I want to do when I run a report I may not want all 20,000+ companies, I may want to filter them for just lets say Construction as the industry, then it will only give me a report for whom are in construction. I can filter it down from there....
But what I dont want is the same business listed more than once as a lot of businesses i have in my db have mutipule industry, roles, and functions which creates duplicate companies for each filter option they may have.
I really didnt think it would be this complicated!
Thanks
Dave

[Gicu]

Dave,
Please review this update, I think a simple group by full address should give you what you need (June suggested it towards the beginning of the thread).
Cheers,

[d9pierce1]

I cant thank you enough for the help. This works exactly like I wanted it to.
Again, I cant thank you enough!
God Bless You
Dave

[d9pierce1]

I looked over changes and it appears that you added another filter to form? Is that correct? Just curious so I can change my other reports that are similar to this.
Were any other changes made?
Thanks
Dave

[Gicu]

Hi Dave,
No changes were made to the form, I just added a new group by to the report (by FullAddress) and moved the textbox controls from the detail section to the new FullAddress header section.
Cheers,

[madpiet]

One option is to put the business name into the header, and not in the Detail section.