Create Access Report

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.

 

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

 

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.

 

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