WhereCondition issues in Report. 1

[Rainer2000]

Halla,
I have been looking for a solution to implement a filter into my Reports, so I can use a report in multiple configurations.
( I presenty ase severeal different reports)
It would be a lot easier to handle having only one report and use a filter or query.
Here is what I want:
I intend to open a Conditioned report like:
DoCmd Openreport StDocname, acPreview, Where condition:="Yes=" & [Shipped]
####All recordsets where the field [Shipped] in the database is set to "Yes".
I would then like to add more conditions.
I also tried a query as a filter but the report does not work.

I would appreciate any help.

Rainer

 

[dhookom]

If Shipped is a yes/no field, your where condition would be:

  DoCmd Openreport StDocname, acPreview, ,"[Shipped]=-1"

You can add more conditions like:

  DoCmd Openreport StDocname, acPreview, , _
   "[Shipped]=-1 AND [ShipDate] Between #" & Me.txtStart & "# AND #" & Me.txtEnd & "#"

Duane
Hook'D on Access
MS Access MVP

 

[Rainer2000]

Hallo Hook'D

Thank you for your fast post. I will try it out tomorrow morning.
Shipped is not a Yes/No field, but I will try something like
"[Shipped]= "Yes" AND [Company]= "GM" AND---
It is too late for tests this evening in Germany.

Rainer

 

[dhookom]

If the field is text then your code might look like:

"[Shipped]= 'Yes' AND [Company]= 'GM' AND---

Normally, I try to pull criteria from controls on forms.

Duane
Hook'D on Access
MS Access MVP

 

[Rainer2000]

Dear dhookom
Thanks! this works fine for me. What would it look like pulling the criteria from the form?

Rainer

 

[dhookom]

Assuming you have a command button or similar that opens the report and have begin and end text boxes as well as a check box (chkShipped) to allow selection of shipped or not. Your code might look something like:

Dim strWhere as String
Dim StDocName as String
stDocName ="rptMyReportName"
strWhere = "1=1 "
[green]' apply filter for Shipped or not[/green]
strWhere = strWhere & " and [Shipped] = '" & _
   IIf(Nz(Me.chkShipped, 0) = -1, "Yes" , "No") & "' "

[green]' apply filter for SaleDates if user entered criteria[/green]
If Not IsNull(Me.txtBeginDate) Then
   strWhere = strWhere & " AND SaleDate >=#" & _
      Me.txtBeginDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
   strWhere = strWhere & " AND SaleDate <=#" & _
      Me.txtEndDate & "# "
End If
[green]' open report with where condition[/green]
DoCmd Openreport StDocname, acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP