Report filter

  • Thread starter Thread starter John B
  • Start date Start date
J

John B

Hi,

I would like to filter a report based on both
- OrderID
- DataRange

Here part of the code. I am Ok with OrderID filter, but when I try to add
the DataRange I got run-time error 3075 and a sintax error: missing
operator.
Can you please help me to understand where am I wrong?

Dim DataRange As String
DataRange = "[DataOrder] Between #" & [Forms]![frmReport]![txtdatefrom] & "#
And #" & [Forms]![frmReport]![txtDateTo] & "#"

rpt.Filter = "[OrderID]=" & ![OrderID] & DataRange

rpt.FilterOn = True
DoCmd.Close , , acSaveYes

Thanks and Regards
John
 
Hi,

I would like to filter a report based on both
- OrderID
- DataRange

Here part of the code. I am Ok with OrderID filter, but when I try to add
the DataRange I got run-time error 3075 and a sintax error: missing
operator.
Can you please help me to understand where am I wrong?

Dim DataRange As String
DataRange = "[DataOrder] Between #" & [Forms]![frmReport]![txtdatefrom] & "#
And #" & [Forms]![frmReport]![txtDateTo] & "#"

rpt.Filter = "[OrderID]=" & ![OrderID] & DataRange

rpt.FilterOn = True
DoCmd.Close , , acSaveYes

Thanks and Regards
John
Click to expand...

You don't need most of what you have so far.
All you need do is open the report using a Where clause in the
OpenReport method to filter the records.
Try it this way.

DoCmd.OpenReport "ReportName", acViewPreview, , "[OrderID] = " &
Me.[OrderID] & " and [DataOrder] >= #" & Me![txtdatefrom] & "# and
[DataOrder] <= #" & Me![txtDateTo] & "#"

The above assumes the name of the form this code is written in is
"frmReport", that [OrderID] is a Number datatype field, and that
[DataOrder] is a Date datatype field.
 
size change
John B said:
Hi,

I would like to filter a report based on both
- OrderID
- DataRange

Here part of the code. I am Ok with OrderID filter, but when I try to add
the DataRange I got run-time error 3075 and a sintax error: missing
operator.
Can you please help me to understand where am I wrong?

Dim DataRange As String
DataRange = "[DataOrder] Between #" & [Forms]![frmReport]![txtdatefrom] & "#
And #" & [Forms]![frmReport]![txtDateTo] & "#"

rpt.Filter = "[OrderID]=" & ![OrderID] & DataRange

rpt.FilterOn = True
DoCmd.Close , , acSaveYes

Thanks and Regards
John
Click to expand...

John,
I think you need to end up with something like (from my test example)
header = 10 and [date] between #2008/01/01# and #2008/12/13#

So,
rpt.Filter = "[OrderID]=" & ![OrderID] & DataRange becomes more like

rpt.Filter = "[OrderID]=" & ![OrderID] & " AND " & DataRange

Hope this helps.
 
Last edited:
Thanks Fred, for your attention, but I still get error message.
Herewith the entire code that output a SNP file of each page of the report:
The name of the form this code is written in is "frmReport",
- OrderID] is a Number (autonumber PK long integer) datatype field,
- [DataOrder] is a Date datatype field



Private Sub PagetoSNP_Click()
Dim dbf As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strPathName As String
Dim rpt As Access.Report

Dim DataRange As String
DataRange = "[DataOrder] Between #" & [Forms]![frmReport]![txtdatefrom] & "#
And #" & [Forms]![frmReport]![txtDateTo] & "#"

strPathName = "C:\Output\"
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("Invoice") 'Qry Invoice

With rst
If .RecordCount = 0 Then
MsgBox "No data to print"
Else
.MoveLast
.MoveFirst
DoCmd.Echo False

Do While Not .EOF
DoCmd.OpenReport "rpt_Invoice", acViewDesign
',,DataRange --> I tried here with no results
Set rpt = Reports("rpt_Invoice")
rpt.Filter = "[OrderID]=" & ![OrderID] & DataRange '<-
run-time error 3075 and a sintax error: missing operator.
rpt.FilterOn = True
DoCmd.Close , , acSaveYes

strFileName = strPathName & ![OrderID] & "_" & ![LastName] &
".snp"
DoCmd.OutputTo acOutputReport, "rpt_Invoice", acFormatSNP,
strFileName
.MoveNext
Loop
Set rpt = Nothing
DoCmd.Echo True

.Close
End If
End With
Set rst = Nothing
End Sub

Thanks and Regards
John
 
Back
Top