Re: first attempt at a report - HELP!

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 13:46:37 +0800

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

-- 
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.
"Miranda" <mirandapbourke@hotmail.com> wrote in message
news:u0Hp$mS8DHA.2676@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:402b0410$0$1725$5a62ac22@freenews.iinet.net.au...
> > 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.
> >
> > -- 
> > 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.
> > "Miranda" <mirandapbourke@hotmail.com> wrote in message
> > news:eDq2TDR8DHA.1596@TK2MSFTNGP10.phx.gbl...
> > > 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
> everyda
> > y
> > > 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

Relevant Pages

  • Re: A really really simple report
    ... An Access MDB file can be located almost ... You generally see the 'database window' when you ... With regard to 'Saving the Query as a report', that's not quite what it does ...
    (microsoft.public.access.gettingstarted)
  • Re: 07 prompts for table field
    ... Still in the code window, choose Compile from the Debug menu. ... Given that the report was generated via Report Wizard and that field is not ... Presumably you have already tried running the source query directly to ...
    (microsoft.public.access.reports)
  • Re: Extract numbers from a string
    ... >> make table query but don't know how to call a module ... >> from a string that I can adapt for my sitation, ... >In the Database Window, ... >All you should have in the code window now is ...
    (microsoft.public.access.queries)
  • RE: how can excell extract data from Access (hot line?)
    ... report to make certain that you are using the same number as other ... Database Management Systems, such as Oracle. ... Excel can retrieve data from an Access query just as easily as ... see the database window with all of the database objects ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Microsoft Cleanliness
    ... I would press Ctrl+F6 to go to the next window, ... through the form and report and code windows until I got to the ... separate VBE, the code window I'm working in is always on top. ...
    (comp.databases.ms-access)