show all records if nothing selected on form 1

[shards]

I have a pop up form (fp_training_rpt_select) where the users have options to input data they want to print a report based upon (unbound, form selection fields are: employee, year_start, year_end, title).

Ultimately, If the user selects nothing, then I want all records to show on the report (r_training) when they click the print button (cmdPrint). If the user selects something that I want the report to filter based on what has been selected when they hit the print button.

I have tried writing IIf statements on the corresonding fields in the query (q_training_rpt) to print all if none but cannot seem to make this work.

FYI...
-unbound control employee should drive filter on field employee_number in query
-unbound controls year_start and year_end should have a Between criteria on the Year field in the query
-unbound control title should drive filter on field title in query

Is there a better way to handle this? Any suggestions on where and how to write the code for this?

Thanks!

 

[dhookom]

If all the criteria fields are in the report's record source, I don't use expressions like "Forms!frmName!txtName" in the criteria in the query. I use code like the following:

Dim strWhere as String
Dim stDocument as String
stDocument = "r_training"
strWhere = "1=1 "
If Not IsNull(Me.cboEmployee) Then
   'assumes EmpID is numeric
   strWhere = strWhere & " AND [EmpID] = " & _
       Me.cboEmployee
End If
If Not IsNull(Me.txtStartYear) Then
   strWhere = strWhere & " AND [Year] >= " & _
       Me.txtStartYear
End If
If Not IsNull(Me.txtEndYear) Then
   strWhere = strWhere & " AND [Year] <= " & _
       Me.txtEndYear
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[shards]

Thanks...very helpful. I assume this all goes on the OnClick control on the form, correct?

 

[shards]

I answered my own question on the last item above.

However, I am a bit lost on what changes when i want to reference text instead of a number. Title is text...I need to filter on that as well.

 

[dhookom]

Dim strWhere as String
Dim stDocument as String
stDocument = "r_training"
strWhere = "1=1 "
If Not IsNull(Me.cboEmployee) Then
   'assumes EmpID is numeric
   strWhere = strWhere & " AND [EmpID] = " & _
       Me.cboEmployee
End If
If Not IsNull(Me.txtStartYear) Then
   strWhere = strWhere & " AND [Year] >= " & _
       Me.txtStartYear
End If
If Not IsNull(Me.txtEndYear) Then
   strWhere = strWhere & " AND [Year] <= " & _
       Me.txtEndYear
End If
' title is text and wildcards are desired
If Not IsNull(Me.txtTitle) Then
   strWhere = strWhere & " AND [Title] Like ""*" & _
       Me.txtTitle & "*"" "
End If
' exact match of Dept text field
If Not IsNull(Me.txtDept) Then
   strWhere = strWhere & " AND [Dept] = """ & _
       Me.txtDept & """ "
End If

DoCmd.OpenReport stDocument, acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[shards]

FABULOUS. I was having a difficult time with the quotation placement. THANKS!!!

 

[FancyPrairie]

You might want to check out this faq181-5497 It contains a function that scans all of the controls on your form and returns the Where clause based upon what was selected. The FAQ explains how to set things up. But, basically, the function BuildWhere looks at the Tag property of each control to determine what to do. It works on list boxes, combo boxes, date ranges, option groups, and more.

DoCmd.OpenReport stDocument, acPreview,,BuildWhere(me)

 

[dhookom]

FancyPrairie's solution is very nice once you understand the basics of using it. It is definitely worth reading through the code to see how this is done.

Duane
Hook'D on Access
MS Access MVP

 

[shards]

Thanks to you both. This is good stuff!!