first attempt at a report - HELP!

  • Thread starter Thread starter Miranda
  • Start date Start date
M

Miranda

hi guys, i'm v. new to reports. I have a report that shows some equipment
usage details (equipName, numberUsed, DateUsed). I have a form which loads
the report - on this form someone enters two dates: FromDate and ToDate i.e.
the period of equipment usage which should be shown on the report (based on
DateUsed). I want my report to be broken up into sections based on everyday
between the two dates entered on my form. So even if no equipment was used
on a particular day in between those dates, i want a blank section to be
displayed...i hope this makes sense!

i hope someone can help me!

thanks in advance,
miranda
 
Hi Miranda.

The dates have to come from somewhere:
1. Create a new table to hold the dates.
2. In table design, create a field named (say) TheDate, Field Type of
Date/Time.
3. Save the table as (say) tblDate.
4. Enter all the necessary dates into this table. (Post back if you want a
piece of code to create all the date entries for you.)

Now you need a query to provide these dates to the report:
1. Create a new query containing both tblDate and your other table.
2. In the upper pane of the query design window, drag tblDate.TheDate onto
the date field in your other query. Access creates a line between the 2
tables.
3. Double-click this line. Access gives you 3 options. Choose:
All records from tblDate, and any matches from ...
4. Drag TheDate from tblDate into the query. Make sure your FromDate and
ToDate get applied to this field, not to the date field from your other
table.
5. Save the query.

Now to use the query in the report.
1. Open the report in Design view.
2. Open the Properties box (View menu).
Ensure the Title of the Properties box says "Report".
3. On the Data tab, set the RecordSource to the name of the new query.
4. Open the Sorting And Grouping dialog.
5. Specify TheDate as the field, and in the lower pane of the dialog, set
Group Header to Yes. Grouping on each value gives you a heading for each
date.
 
Reports don't print a detail section for something that isn't there, so
you'll need to create a query that includes a table with a record for each
date in the range. Then you will have a record for each date, so you can
print something, although not all of them will necessarily have any data
other than the date.

Larry Linson
Microsoft Access MVP
 
hi allen,

thanks for your help so far! could you tell me how to do step 4 i.e. enter
all the dates in the table

thanks again
 
1. Select the Modules tab in the Database window, and choose New.
Access opens a code window.

2. From the Tools menu, choose References, and check the box beside:
Microsoft DAO 3.6 Library.

3. Paste the code below into the Code window, under the Option statement(s).

4. Change the dates on the "For dt ..." line to cover the dates you need.
(The exmple creates dates for 2002, 2003, 2004, and 2005.)

5. Open the Immediate Window (Ctrl+G), and enter:
? MakeDates()

--------------code begins------------------
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2002# To #12/31/2005#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--------------code ends------------------
 
Back
Top