I forget what sections apply to grouping and page breaks...

[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.

 

[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!

 

[accessuser1023]

luckily it was *not* variable in this case!