1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Create Access Report

Discussion in 'Business Applications' started by JF Sly, Apr 6, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. JF Sly

    JF Sly Thread Starter

    Joined:
    Apr 6, 2004
    Messages:
    11
    I am using Access 2000 and Access 2003.

    I have a crosstab query. I would like to have a command button on a form that will create a report with the crosstab query as the record source, then add the all the fields to the report. I can not use a static report becasue as each week goes by, the crosstab query will add a new "column".

    The fields in the crosstabb query are:
    TeamName
    AgeGroup
    League
    GameDate
    Points

    Points are summed in the crosstab query. I have attached screenshots of the query and a manually created report.
     

    Attached Files:

  2. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    As most of the time a query populates the form, I'm not sure how would achieve this - especially since, even with code, it would be hard to create a revised query without knowing what the field name of the new field is, or in your case, the field name being a date. You might be able to come up with some code to create a SQL statement for a query on the fly if you knew in advance what the field name would be.

    The easiest way I can think of is to ensure that your fields exists (even if you have to name them game1, game2, so on) and then for your report, create a line of code that makes the field visible if the contents is not null/equal to or greater than 0/etc.

    That way, not only are you viewing only those games that have taken place, but you are setting up the formatting of your report to fit all games for the season.

    the way you want to do it now, you would still have to go in and make sure that the report will fit on the page.

    Hope this helps,

    MBN
     
  3. JF Sly

    JF Sly Thread Starter

    Joined:
    Apr 6, 2004
    Messages:
    11
    The query does not need to be done on the fly. The query is already built. I just need to be able to create the report on the fly and populate the report with the data from the existing query. The query will automatically add the field each week.

    I know there is a CreateReport() method. I can get that pare to work (creates a blank report in design view. I can also set the source for the report to the query.

    I do not know how to get the fields to populate into the report, create the Group Level 1 and Group Leve 2 sections (and put the proper fields into those groups.
     
  4. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    I repeat, the only way you are going to be able to create a query or report on the fly is if you know the name of the fields that are added because you would need some way to reference them in your code.

    Then, with some playing with the VB code, you could determine whether that field should be displayed.

    Example (and only an example):

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Location <> "" Then
    Me.Location.Visible = True
    End If
    End Sub

    This code, obviously, will only show the field if its contents is not Null. If those fields that are being added are always named the date involved,then you might want to try some code that checks the name of the field; if it's a date, then add it to the detail section of the form.

    MBN
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217819

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice