dynamic reports from forms (1 Viewer)

[hockey8837]

Hi,
I'm trying to generate a report off of a form with combo/list boxes that will filter the report parameters. I'm doing this for several forms/reports, but generally need the same thing for all.

For example, I have a form for my programs which has a combo box "cboYear" to select a year, based off of [progdate] and formatted as "yyyy" so the user can select what year they want their reports from. But, I also have a cascaded combo box "cboProgramTitle" that is based off of the [progdate] box, and only shows programs selected for the "cboYear". The code below will run the report and filter if something is selected for "cboProgramTitle", or returns all if left blank. How do I also get it to return all programs for the "cboyear", since this is already a 'formatted' version of [progdate], if a year is selected, but "cboProgramTitle" is left blank?

Private Sub cmdAttendees_Click()
If IsNull(Me.cboProgramTitle) Then
   DoCmd.OpenReport "rptProgramAttendees", acViewReport
Else
   DoCmd.OpenReport "rptProgramAttendees", acViewReport, , "ProgramIDFK = " & cboProgramTitle & ""
End If

End Sub

I've attached a sample of my DB, the form in question can be found by clicking the command button in the 'programs/events' tab. I've run into this same problem for the forms/reports in the 'tours' and 'tz' tabs, too. Need help filtering reports off multiple combo/list boxes, with one of them being the year list, or others being month or quarter lists.

Thanks in advance!

 

[David R]

You're so close, you have the right theory here but not the trick of it.

The trick is to build your WHERE clause as a string variable, piecing it together with AND or OR as makes sense. That's where your IF statements get used.

Then, at the end of all that concatenation, you run the IF you've got listed above, except it's more like:

If IsNull(strFilter) Then
   DoCmd.OpenReport "rptProgramAttendees", acViewReport
Else
   DoCmd.OpenReport "rptProgramAttendees", acViewReport, , strFilter
End If

 

[hockey8837]

Hi David,
I'm having trouble figuring out what that 'where' clause should be for my dates, since I'm pulling the year from the [progdate], but need to apply that year to the [progdate] when it goes back to my report.

I'm what I call a 'novice frankenstiner', in that I can 'read' the code and piecemeal together what I usually need, but can't write it! This one's got me stumped, and I've spent 2 days web searching with no real good examples to be found.

 

[David R]

No problem. Can you give a couple of examples of what would and wouldn't fit within an example filter set? Something like "If I set the dates to Between 1/1/2013 And 12/31/2013, the valid program titles are Cheetah Racing, Porcupine Petting, and Elephant Wrestling. Tiger Baiting ended in 2012 (as shown in tableX.FieldY) so it should be excluded". Any other details that would help too...

 

[hockey8837]

So, the programs report would pull names for the [programtype] of 'special events', and [programname] would be 'boo at the zoo' or 'noon year's eve. Or, another [programtype] would be 'family programs', with [programname] having 'zoo kids', 'family workshops,' or 'baby and me' etc... The DB I uploaded does have some sample programs already in there that should pop up so that when you play around with it there is some data to be had.

Basically, I'd be looking to run reports monthly, quarterly, and annually-but I may want to do this by [programtype] (like, all special events), [programname] (like all 'baby and me' programs), or, I may need to do this for one program (like just for 'noon year's eve' which happens once a year, but I may want to compare it to last year's event #s).

Hopefully this makes sense? As you may guess by the subject of the DB, database building is not my primary career! So I appreciate it!

Also, the BEST programs are when the tigers wrestle the elephants!

Thanks for your help!

 

[David R]

So to be perfectly clear, do you ever need to limit by more than one criteria at once? Say, only Special Events this Calendar year?

If not, this should be pretty simple. Something like:

Dim searchStr As String

    If Not IsNull(Me.cboYear) Then strFilter = strFilter & "Year([ProgDate]) = " & Me.cboYear & " OR "
    If Not IsNull(Me.cboProgramTitle) Then strFilter = strFilter & "[ProgramName] = " & Chr(39) & Me.cboProgramTitle & Chr(39) & " OR "
    If Not IsNull(Me.cboProgramType) Then strFilter = strFilter & "[ProgramType] = " & Chr(39) & Me.cboProgramType & Chr(39) & " OR "

    If searchStr = "" Then
       DoCmd.OpenReport "rptProgramAttendees", acViewReport
    Else
       
       DoCmd.OpenReport "rptProgramAttendees", acViewReport, , Left(strFilter, Len(strFilter) - 4)  'remove the final " OR "
    End If

Note that strFilter is blank until it hits a criteria field that is filled out, so strFilter = strFilter & "blah" ... is equivalent to strFilter = "blah" the first time!

If if gives you trouble, throw a breakpoint in there and/or put a Debug.Print strFilter in right before you run the report, to see what's wrong. Note that matching text fields will require the ' that Chr(39) gives you (you can also hardwrite it in your code, but I find it's hard to see next to the "s).