Searching and Reporting

With the date type in the Access database set to Short Date we can produce a report based on the date that invoices were created. As usual we create a simple form for entry of the search criteria and link this to the menu in the parent form:

Next we produce a report form:

We add an ADOQuery item and set its SQL property to:

SELECT Invoices.[Invoice ID], Invoices.Date, Invoices.Payee, Invoices.[Total Cost]
FROM Invoices
WHERE Date = :mydate;

The mydate parameter is automatically picked up by the ADOQuery item.

Finally we write the code that performs the search:

procedure TForm3.Button1Click(Sender: TObject);
begin
 with QuickReport2 do
 begin
  ADOQuery1.Parameters[0].Value:=StrToDate(Edit1.Text);
  ADOQuery1.Open;
  ADOQuery1.Requery;
  if ADOQuery1.RecordCount=0 then showmessage ('No records found')
  else Preview;
 end;
 Close;
end;

The StrToDate function converts a string to short date format so the underlying data in Access should be in the same format.

We can also create a report to display invoice details from the three tables:

From this simple example you should be able to construct queries involving two dates and a display of invoices raised between them.

To produce a detailed invoice, showing some items as header information and others as repeating items create a query in Access that includes all fields in the Invoices, Invoice Items and Item Details tables. You can then edit the SELECT clause to make it more easy to read and add the WHERE clause as before:

SELECT Invoices.*, [Invoice Items].*, [Item Details].*
FROM [Item Details] INNER JOIN (Invoices INNER JOIN [Invoice Items] ON Invoices.[Invoice ID] = [Invoice Items].Invoice) ON [Item Details].[Item ID] = [Invoice Items].[Item ID]
WHERE Invoice.[Invoice ID] = :inv_param;

(Access rejects the code in this form but runs it when it is submitted by Delphi.) The code to run this from a button on a form is almost the same as we have used before:

procedure TForm5.Button1Click(Sender: TObject); 
begin
 with QuickReport4 do
 begin
  ADOQuery1.Parameters[0].Value:=strtoint(Edit1.Text);
  ADOQuery1.Open;
  ADOQuery1.Requery;
  if ADOQuery1.RecordCount=0 then showmessage ('No records found')
  else Preview;
 end;
 Close;
end;

It might be useful to copy the invoice number from the main form into this subsidiary form so that the user has no need to remember (the user can always enter a different value):

procedure TForm5.FormCreate(Sender: TObject);
begin
 edit1.text:=form1.DBEdit1.Text;
end;

This report is quick and cheerful, more time could be spent polishing it to a more acceptable standard.

Exporting to Other Applications

This is something you may want to do if you need to get data from a part of the database to another application. From Access you could use Copy/Paste or File/Export, from Delphi you can use the ADOConnection.Execute command. To export a single table create an entry in the menu system of the main form and attach some code like this:

procedure TForm1.Spreadsheet1Click(Sender: TObject);
begin
 ADOConnection1.Execute('SELECT * INTO Invoices IN "Invoices.xls" "Excel 8.0;" FROM Invoices');
end;

This creates a file in the same directory as the application called 'Invoices.xls':

The SQL command copies the data from the query into a new table and then saves this as the file specified. Notice that the SQL code is executed in the Access database through the ADOConnection and the Jet database engine then performs the export to Excel format.

The following more complex query was generated in Access and is the same as the code used above to generate a copy of an invoice in the Delphi report:

procedure TForm1.Invoicedetailstospreadsheet1Click(Sender: TObject);
begin
ADOConnection1.Execute('SELECT Invoices.[Invoice ID], Invoices.Date, Invoices.Payee, Invoices.[Total Cost],' +
  '[Invoice Items].[Invoice Item], [Invoice Items].[Item ID],' + 
    '[Item Details].Description, [Item Details].[Unit Cost], [Invoice Items].Quantity, [Invoice Items].[Total Cost]' +
   'INTO InvoiceDetails IN "Invoice_Details.xls" "Excel 8.0;" FROM [Item Details]' +
     'INNER JOIN (Invoices INNER JOIN [Invoice Items] ON Invoices.[Invoice ID] = [Invoice Items].Invoice)' +
      'ON [Item Details].[Item ID] = [Invoice Items].[Item ID]');
end;

This creates a file called 'Invoice_Details.xls' in Excel format so a user could work with the data in that format, copy it into a word processor, and so on.

One problem with exporting this way is that an error is raised when the file exists so we ought to delete any previous copies first.

The Jet database engine will also export data in HTML format:

procedure TForm1.Invoices1Click(Sender: TObject);
begin
 ADOConnection1.Execute('SELECT * INTO [Invoices.htm] IN "X:\Delphi examples\invoice_database3" "HTML Export;" FROM Invoices');
end;

This page could be linked to a web server or converted to XML for transmission between organisations.

At this point you have to ask what facilities your user wants and what should be built into the application - these should have been established before you started the database design. The report format provides a ready-made means for displaying printed output while exporting to other formats suggest the user will have more knowledge and skills to manipulate the data.

Database Home