Re: Adding blank lines in a report
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 02/18/05
- Next message: johnny130553: "Re: text box control source"
- Previous message: \: "Re: problem - urgent"
- In reply to: Steve Hart via AccessMonster.com: "Adding blank lines in a report"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 18 Feb 2005 13:20:21 +0800
The extra lines have to come from somewhere. The simplest source is another
table that tells Access about the missing numbers.
1. Create a new table, containing just one field named CountID, of type
Number (Long Integer). Mark the field as the primary key (toolbar icon).
Save the table as tblCount.
2. Enter the records into this table manually, or use the function below to
enter 1000 records instantly.
3. Create a query that contains both this table and the table containing
your data.
4. In the upper pane of the query window, drag the number field of your
table onto the CountID field in tblCount, and release the mouse. Access will
create a line joining the two tables.
5. Double-click the line you just created. Access pops up a dialog offering
3 options. Choose the one that says:
All fields from "MyMainTable", and any matches from "tblCount".
6. Drag tblCount.CountID into the output grid. In the Criteria row beneath
this field, specify less than or equal to the highest number in your
collection. For example, if you collection goes up to 80, specify:
<= 80
7. Drag all the fields from your main table into the query grid as well.
8. Save the query, and use it as the RecordSource for your report.
Function MakeData()
'Purpose: Create the records for a counter table.
Dim db As Database 'Current database.
Dim lng As Long 'Loop controller.
Dim rs As DAO.Recordset 'Table to append to.
Const conMaxRecords As Long = 1000 'Number of records you want.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly)
With rs
For lng = 1 To conMaxRecords
.AddNew
!CountID = lng
.Update
Next
End With
rs.Close
Set rs = Nothing
Set db = Nothing
MakeData = "Records created."
End Function
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Steve Hart via AccessMonster.com" <forum@AccessMonster.com> wrote in message news:67deef55c5c747b298456c817c411131@AccessMonster.com... > I'm creating a report that is basically a listing of all of the Old Time > Radio programs I have of a certain series. The basic report is working > fine, but I'm getting fancy and that's where I'm getting over my head. > > Each episode has a number and they are sorted in ascending order. What I > would like to do is add a blank line anywhere in the report that an > episode > is missing from my collection. For example: > > #1 > #2 > #3 > > #5 > #6 > > #10 > #11 > > > Is there a way to do this? > > Steve > > -- > Message posted via http://www.accessmonster.com
- Next message: johnny130553: "Re: text box control source"
- Previous message: \: "Re: problem - urgent"
- In reply to: Steve Hart via AccessMonster.com: "Adding blank lines in a report"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
