I forget what sections apply to grouping and page breaks... (1 Viewer)

[accessuser1023]

all,

It's been so long since I've done this, can you guys help me out here? I have a table that has invoice line items in it. Each line item has the same invoice number to it. I am creating a report, but I need to group the records by Invoice # so that, after each # (and all the line items in the detail section), I get a page break and go on to the next Invoice.

Can you someone give me a boost here on this? I forget which section is which and what they are used for. Here are the sections that I current have on the report:

=> page header (an image)
=> Invoice header (bound field information here)
=> detail (line item records here)
=> page footer (page total fields and an image)

where would I place my page break to get what I'm looking for? My total fields are also erroring out on me and display "#Error" with the formula "=sum([field])" in them. Am I missing something there too?

thanks guys.

 

[Pat Hartman]

The force new page property should be set for the Invoice header section.

"field" must refer to a field in the report's RecordSource. It cannot refer to a control. So, if a control includes a calculation, you need to repeat the calculation in the Sum() function. So contrlX has a ControlSource of:
= fld1 * fld2
The control in the footer that sums control would have a ControlSource of:
=Sum(fld1 * fld2)

=Sum(control) would be incorrect and raise an error.

 

[accessuser1023]

thanks Pat! I used your suggestion and it worked just fine.

One more question for you? For some reason I'm also having trouble sorting by a field with my grouped report. Don't know why. Here's the deal:

Rpt is opened by a form and the WHERE is driven by a multi-select listbox:

Dim numInvoice As Double
Dim sql As String
Dim sqlWhere As String
Dim sqlOrderBy As String

sql = "SELECT * " & _
      "FROM [" & Me.cmbdatasets & "] " & _
      "WHERE "
sqlWhere = ""

  For i = 0 To Me.lstinvoices.ItemsSelected.Count - 1
    numInvoice = Me.lstinvoices.Column(0, Me.lstinvoices.ItemsSelected(i))
    sql = sql & "[Invoice #] = " & numInvoice & " OR "
    sqlWhere = sqlWhere & "[Invoice #] = " & numInvoice & " OR "
  Next i
    sql = Left(sql, Len(sql) - 4) 'trim loop tailings.
    sqlOrderBy = "ORDER BY [Start Date], [End Date]"
    sql = sql & " " & sqlOrderBy 'add ORDER BY clause.

      DoCmd.OpenReport "rptinvoice", acViewDesign
      Reports("rptinvoice").RecordSource = sql
      DoCmd.OpenReport "rptinvoice", acViewPreview

but in the detail section the records are not being sorted as I'm asking. Missing something here? Do you also think I should use filters in the report for the "where" clause instead of rolling all of it into the SQL string? I have checked the table too, to see if anything over-riding it would be affecting it. There is nothing. I also realize that my method isn't the best, but none-the-less it works fine.

thanks.

 

[accessuser1023]

I'm sorry Pat. I wasn't using the sorting and grouping wizard in the report. Issue solved. I can't believe I didn't think of that. I'm just too much of a developer for my own good!

 

[Pat Hartman]

Access is smarter than we are (or at least it thinks so) and so it rewrites the report's recordSource query and ignores any order by clause. That's why your code didn't work. I'm pretty sure you can set the sorting property with code but I don't have any example handy. As long as it isn't variable, then using the sorting and grouping dialog is the correct method.

 

[accessuser1023]

luckily it was *not* variable in this case!