Order a report

[hdesbiens]

Hi

i have a criteria window that have many sélection fields on it like this:

- client
- startdate
- enddate
- order number
- status

so by building a request in the code with these criteria my report shows the right records.

But this is very long and first i want to know if there a way to filter the records showing on a report?

And secondly i want to add another criteria that will order my report by the fields i want?

is there a way to do that very short?

thanks

 

[Remou]

Have you seen (?):
Build Report Criteria via a Form w/list box, text box, date range
faq181-5497

 

[rjoubert]

For the filter, specify a Where parameter in your call to DoCmd.OpenReport.

DoCmd.OpenReport "Report1", acViewPreview, , "[client]='test'"

 

[hdesbiens]

non thanks for the reference

but im looking for a easier way to to ordering and filtering a report is there one?

like this :
report.orderby = "client"

just like we can filter a datasheet form?

thanks

 

[rjoubert]

After opening the report, you can affect the OrderBy property...

DoCmd.OpenReport "Report1", acViewPreview
reports!Report1.OrderBy = "client"

If this doesn't work, check the design of your report to ensure the OrderByOn property is set to Yes.

 

[Remou]

It is possible to reference a form when building the report:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = Forms!frmForm!txtFilter
Me.FilterOn = True
Me.OrderBy = Forms!frmForm!txtOrderBy
Me.OrderByOn = True
End Sub

If the report is to be similar to a form, you may wish to get OrderBy and Filter from the form, in which case:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = Forms!frmForm.Filter
Me.FilterOn = Forms!frmForm.FilterOn
Me.OrderBy = Forms!frmForm.OrderBy
Me.OrderByOn = Forms!frmForm.OrderByOn
End Sub

Is this nearer to your idea?

 

[hdesbiens]

Thanks rjoubert and Remou

thats great, but im asking another question, can we apply more than one order and filter on the same report?

and how?

thanks a lot

 

[rjoubert]

For the filter, you WHERE parameter is just like a WHERE clause on a SQL statement, so you can add as many fields as you want.

DoCmd.OpenReport "Report1", acViewPreview, , "[client]='test' AND [otherfield]=2 AND [date]=#1/1/2006#

 

[rjoubert]

For the sorting, just comma delimit your fields to sort by...

reports!Report1.OrderBy = "client, date, otherfield

 

[dhookom]

I would never use the OrderBy property of the report. I find it much more robust to set the ControlSource properties of the pre-defined grouping levels. The code to do this can be found at

http://www.allenbrowne.com/ser-33.html.

If you have defined any sorting and grouping levels, it doesn't have any effect when setting the OrderBy.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hdesbiens]

Thanks to all for your help

but i need another hint

if i use this syntax for filtering my report

DoCmd.OpenReport "Report1", acViewPreview, , "[client]='test' AND [otherfield]=2 AND [date]=#1/1/2006#"

the filter clause is it possible to build it dynamycally with those conditions:

If IsNull(Me.NoClient.Column(0)) Then
StrFilter = ""
Else
StrFilter = "[client]=" & "'" & Me.NoClient.Column(0) & "'"
End If

If IsNull(Me.startdate) Then
StrFilter = StrFilter & ""
Else
StrFilter = StrFilter & Me.startdate
End If

If IsNull(Me.enddate) Then
StrFilter = StrFilter & ""
Else
StrFilter = StrFilter & Me.enddate
End If

so when my conditions is built i will have only to put strfilter here like this:

DoCmd.OpenReport "Report1", acViewPreview, , strfilter

Is it possible to do this ?? i got some errors!!

thanks a lot

 

[rjoubert]

Yes you can do that...I suspect your error is a result of the way you're formatting the date portions.

Instead of...

StrFilter = StrFilter & Me.startdate

Use...

StrFilter = StrFilter & "#" & Me.startdate & "#"

Then do the same thing for the enddate.

 

[hdesbiens]

thanks a lot

 

[hdesbiens]

Now that all my filters are working, my problem is on the order sentence:

Reports!RapportAchats.OrderBy = "NomClient" it works no error when executing the line but my report is not ordered!!

i dont know why???

Thanks for your help

 

[dhookom]

Did you set OrderByOn = True?

Again, if you have defined any sorting and grouping levels, it doesn't have any effect when setting the OrderBy.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]