Data Report Help

I am a bit out of my depth here. I started a simple accounts program that enters information into a MS Access database, so far so good. As well as enter the data I can view records one at a time and edit or delete them.

Now I need to make a report. My main table has fields holding the following: id, date, category, supplier, amount, notes. Firstly I want to create reports between a user selectable start date and end date. On the report I want to list all the records between these dates, ordered by categories, and show a total of the amounts under each category as well as a final total at the end.

I have been trying to use Data Reports and can generate a report showing all the fields, but so far it shows all records. I have no idea how to add a start date and end date, or how to add in totals.

Any advice would be greatly appreciated.

To put in totals / categories, you will need to create group headers / footers in your report.

You will also need to look at using Shape, to create a recordset which fits the structure of your report.

You can specify the report source of the report, before openning it i.e. create a record source and then say

Code:

---

Set rpt.DataSource = adrQry

---

where rpt is the name of your report, and adrQry is the name of your recordset.

HTH

Thanks for the help. Now I am getting errors "Report width is larger than the paper width". How would I stop this? I have almost nothing in the report at the moment.

Oh. I figured that one, I had a line on the report that was stopping me resize it. Guess I better do some readin on the Group Headers / Footers.

I have no idea how to use the group headers/footers. I drag a field into a group header and I get a "Report sections do not match data source" error. If anyone can give me a few hints on using the group headers / footers I would really appreciate it.

You need to look for help on the SHAPE command. I only ever use it as an SQL statement, so if you're using it like that I can help, but if you use the data environment, I'm can't help you.

The shape command, basically allows you create different recordsets within the one recordset eg one for each header, and then one for the detail lines.

Sorry I can't be of more help to you.

JP

I have hunted everywhere for info on the SHAPE command. I haven't been able find anything that I can understand. In the mean time I have been getting my code to make basic reports. I am hoping you can give me a few hints on using the shape command. I have 2 calendars to choose a start date and an end date for the report, then the comand button has this code in it.

Code:

---

Private Sub cmdShowReport_Click()
       
    Dim Srchflag As Boolean
    Dim i As Integer
   
    'Setting Initial data for recordset
    rs.Close
    rs.Open "Select * from Expenses WHERE Expense_Date>='" & Calendar4.Value & "' AND Expense_Date<='" & Calendar5.Value & "' ORDER BY Category ;", cnAP, adOpenDynamic, adLockOptimistic
    Set DataReport1.DataSource = rs.DataSource
   
    With DataReport1

        With .Sections("Section1").Controls
                    For i = 1 To .Count
                        If TypeOf .Item(i) Is RptTextBox Then
                            'The datamember should be always blank while creating dynamic data reports
                            .Item(i).DataMember = ""
                            .Item(i).DataField = rs.Fields(i - 1).Name
                        End If
                    Next i
        End With
        With .Sections("Section2").Controls
                    For i = 1 To .Count
                        If TypeOf .Item(i) Is RptLabel Then
                            'The datamember should be always blank while creating dynamic data reports
                            .Item(i).Caption = "Records for period from " & Calendar4.Value & " to " & Calendar5.Value
                        End If
                    Next i
        End With
       
    End With
   
        If rs.EOF <> True Then
                DataReport1.Show
                DataReport1.SetFocus
                Srchflag = True
                Exit Sub
        End If

    If Srchflag = False Then 'Display msg when search not found
        MsgBox "Search Not Found", vbInformation, "Search Result"
    End If


End Sub

---

One of the fields is a Currency amount. What I want to do is put a line underneath each category showing a total value of the category, and then at the bottom put a total of everything. At the moment I have no group headers, my report has 1 RptLabel in the Page Header and 6 unbound RptTextBox's in the Detail Section.

Any hints on how to use the shape command with the above code would be appreciated. Thanks.

have u tried MSDN?
this is what i got from MSDN

Quote:

---

Shape APPEND Clause
The shape command APPEND clause appends a column or columns to a Recordset. Often these columns are chapter columns, which refer to a child Recordset.

Syntax
SHAPE [parent-command [[AS] parent-alias]] APPEND column-list
Description
The parts of this clause are as follows:

parent-command
Zero or one of the following (you may omit the parent-command entirely):
A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
Another shape command embedded in parentheses.
The TABLE keyword, followed by the name of a table in the data provider.
parent-alias
An optional alias that refers to the parent Recordset.
column-list
One or more of the following:
An aggregate column.
A calculated column.
A new column created with the NEW clause.
A chapter column. A chapter column definition is enclosed in parentheses ("()"). See syntax below:
SHAPE [parent-command [[AS] parent-alias]]
APPEND (child-recordset [ [[AS] child-alias]
RELATE parent-column TO child-column | PARAMETER param-number, ... ])
[[AS] chapter-alias]
[, ... ]
child-recordset
A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
Another shape command embedded in parentheses.
The name of an existing shaped Recordset.
The TABLE keyword, followed by the name of a table in the data provider.
child-alias
An alias that refers to the child Recordset.
parent-column
A column in the Recordset returned by the parent-command.
child-column
A column in the Recordset returned by the child-command.
param-number
See Operation of Parameterized Commands.
chapter-alias
An alias that refers to the chapter column appended to the parent.
Note The "parent-column TO child-column" clause is actually a list, where each relation defined is separated by a comma.
Note The clause after the APPEND keyword is actually a list, where each clause is separated by a comma and defines another column to be appended to the parent.

---

sorry can't help u with the code... :p

regards

The following address may help:

MSDN and shape

I'm going to include code here which produces grouped reports. We have a unit stocking system (a garage has cars on their forecourt for sale, we provide the finance for these cars). The garages supply us with orders, we determine if they have available credit etc, and if so we approve the order. They then have to send us an invoice, which we match to the order. Sometimes they decide not to go ahead with the order and forget to tell us, so we never cancel the order. What this program does, is report on orders that don't have invoices, or on orders where the invoice doesn't match the order amount.

The project has two reports, take a look at the second one (rptOrdersWithoutInvoices), and take a look at the event cmdOrdersNoInvoices_Click from the main menu.

The report produces a detail on each individual franchise (read garage) - this is done via the group section.

HTH

Thanks erickwidya for the quote, I did read a lot of that, but it doesn't mean much to me. I have been using a database with VB for about 3 days so far. Try to learn as fast as I can.

Thanks for the sample jp140768, thats exactly what I need. Hopefully I can work out what I need from this.

Hi jp140768,

Sorry to be a pain, when I try to run your code I get an error "Can't find project or library" on this part of the code "Dim objIni As Utilities.clsWinAndIni" What should I do to stop this error?

Sorry the project won't run, it was only meant as an example. To get the project to run I'd have had to send you the database as well as some of the DLLs - definitly can't send you the database.

If you look at the report and the function that I mentioned yesterday, you should see how it works. If not feel free to ask me about it.

Hi, no problem .. I will continue to study the code. I do want to work out as much as I can myself. Thanks.