Using a form to filter my report

[Xx_TownDawg_xX]

I do not need anything fancy, but I'm at my wit's end.

Given a report, and a form to enter my criteria, what is the simplest way to pass that filter to the report?

I've read, I'd tried this and that.. and nothing has worked completely to the point I've erased the form and started over.

The easiest seems to be using "macros" over "code" -- but I will take anything that's easy enough for me to move on and get this part done in the project.

Please let me know what additional information you need. I'm even willing to upload the database if that helps.

 

[pbaldy]

Either have the report based on a query that uses the form in its criteria, or use the wherecondition argument of OpenReport. I don't use macros, but the code for that would look similar to this:

http://www.baldyweb.com/wherecondition.htm

using OpenReport instead of OpenForm of course.

 

[Xx_TownDawg_xX]

[FONT=&quot]Good so far, but if I have two fields?

DoCmd.OpenReport "rpt_DataRpt", , , "[qry_DataRpt]![FiscalMonth]= " & Me.qFiscalMonth And [qry_DataRpt]![tbl_type] = " & Me.qProduct"


[/FONT][FONT=&quot][/FONT]
[FONT=&quot]So looking at this example.. [/FONT][FONT=&quot]What's the correct syntax?

[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem[/FONT][FONT=&quot][/FONT]
[FONT=&quot]'trim trailing comma[/FONT][FONT=&quot]
strWhere = Left(strWhere, Len(strWhere) - 1)[/FONT][FONT=&quot][/FONT]
[FONT=&quot]'open the report, restricted to the selected items[/FONT][FONT=&quot]
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"[/FONT]
[FONT=&quot]
Nice website by the way.. [/FONT]

 

[pbaldy]

You only want form references that are to be evaluated for their values outside the quotes. Presuming numeric values, try:

DoCmd.OpenReport "rpt_DataRpt", , , "[qry_DataRpt]![FiscalMonth]= " & Me.qFiscalMonth & " And [qry_DataRpt]![tbl_type] = " & Me.qProduct

 

[Xx_TownDawg_xX]

no both are strings.

Dec-08 (qFiscalmonth), and Fabrication (qProduct)

 

[pbaldy]

Then try


DoCmd.OpenReport "rpt_DataRpt", , , "[qry_DataRpt]![FiscalMonth]= '" & Me.qFiscalMonth & "' And [qry_DataRpt]![tbl_type] = '" & Me.qProduct & "'"

 

[Xx_TownDawg_xX]

Man I wish I was that smart.. and rattle off the code like you do..

I need to change the code slightly to preview the report first.

DoCmd.OpenReport "rpt_DataRpt", acViewPreview, , "[qry_DataRpt]![FiscalMonth]= '" & Me.qFiscalMonth & "' And [qry_DataRpt]![tbl_type] = '" & Me.qProduct & "'"

?

 

[Xx_TownDawg_xX]

Wow.. it's working.. THANK YOU.

 

[pbaldy]

No problem; I don't think it's being "smart" so much as having done it countless times.

I just noticed your compliment on my site. Thanks! I'm still getting it going so I appreciate the feedback. It's probably obvious that my creative skills are sorely lacking.

 

[MCLEOD]

Paul,

I justed wanted to say thanks! I too was looking for the solution that TownDawg was looking for. Your solution was the cleanest and easiest I've read so far on this forum. Plus your site help me solve my cascading cbo problem.

Thanks again,
Matt

 

[pbaldy]

No problem, Matt; I'm glad it helped you. Welcome to the site!