open report from form

[hockey8837]

Hi,
I'm trying to create a form which filters a report based off of combo boxes selected by the user.

The code I'm using currently is:

DoCmd.OpenReport "rptProgramAttendees", acViewReport, , "ProgramIDFK = " & cboProgramTitle

This works great to return a report if the user selects something from the combo box. How do I adapt this so that the user can also leave the combo box blank and filter the report to return all records?

Additionally, what if I want to have the user filter between dates selected on the form; i.e. between 'txtStart' and 'txtEnd'

Thanks!

 

[Mihail]

If IsNull(Me.cboProgramTitle) Then
   DoCmd.OpenReport "rptProgramAttendees", acViewReport
Else
   DoCmd.OpenReport "rptProgramAttendees", acViewReport, , "ProgramIDFK = [COLOR=Red]'[/COLOR]" & cboProgramTitle & "[COLOR=Red]'[/COLOR]"
End If

Hope you see also the red quotes

 

[Mihail]

OK. My mistake. Your combo return a number not a program title (string) so the red quotes are not necessary.

 

[hockey8837]

Great! This works perfectly! Thanks Mihail!

 

[sdchris]

I have a question. If I want to filter a report based on 5 combo box entries, do I need to run an if statement to check for null values on each combo box? Or can I run an IIF statement within the where clauses to do nothing if null?

This is the code I'm working with at the moment:

DoCmd.OpenReport strOpenReport, acViewReport, , "[Country] = '" & Me.cboCountry.Column(1) & "' And [Business Unit] ='" & Me.cboBusinessUnit.Column(1) & "'"

I haven't added any additional where arguments for the remaining combo boxes. I'd like to get this one to work first prior to adding those. If one of those where boxes references a null combo box then the entire report is empty. I'm wondering if there is an easier way to check for nulls then cycling through a bunch of if loops.

Thanks,
Chris

 

[David R]

Chris, what I usually do is build the WHERE string sequentially, rather than nesting a whole mess of IIFs.

Look at http://www.access-programmers.co.uk/forums/showpost.php?p=1320082&postcount=6

 

[sdchris]

I like the look of that code! I'm going to give it a go.

Thanks,
Chris

 

[sdchris]

David,

For some reason it filters correctly when I have one combo box selection, but when I have multiple combo box selections it displays all records.

Dim strFilter As String
        
    If Not IsNull(Me.cboCountry.Column(1)) Then strFilter = strFilter & "[Country] = '" & Me.cboCountry.Column(1) & "' OR "
    If Not IsNull(Me.cboBusinessUnit.Column(1)) Then strFilter = strFilter & "[Business Unit] = '" & Me.cboBusinessUnit.Column(1) & "' OR "
    If Not IsNull(Me.cboLawFirm.Column(1)) Then strFilter = strFilter & "[Law Firm] = '" & Me.cboLawFirm.Column(1) & "' OR "
    If Not IsNull(Me.cboCompany.Column(1)) Then strFilter = strFilter & "[Company] = '" & Me.cboCompany.Column(1) & "' OR "
    
    If strFilter = "" Then
        DoCmd.OpenReport "DBTest", acViewReport
    Else
        Debug.Print strFilter
        strFilter = Left(strFilter, Len(strFilter) - 4) 'remove the final " OR "
        DoCmd.OpenReport "DBTest", acViewReport, , strFilter
        Debug.Print strFilter
    End If

I tried messing around with the strFilter placement and didn't have any luck. The debug shows this for the before and then after:

[Country] = 'USA' OR [Business Unit] = 'Energy' OR [Law Firm] = 'Nydegger & Associates' OR
[Country] = 'USA' OR [Business Unit] = 'Energy' OR [Law Firm] = 'Nydegger & Associates'

Any thoughts on why the where clause is not filtering correctly?

 

[sdchris]

I think I should be using AND instead of OR. Doh.

 

[bob fitz]

Are you sure that the field called [Law Firm] is a Text type. Could it be number field.

 

[David R]

Yes, if you are trying to use one or more criteria you want AND. The user in the other thread was picking any one criteria to filter by, hence OR.