problem with crosstabs + reports

[Kevin]

Hi
I have created a report based on a Crosstab query
the crosstab query is based on another query
problem i have is that the results this month may be different next
month
having tested this by changing the date i find that the field is not
recongized
how can i design a report that is dynamic i.e.a field may be there
this month but not next month


thanks

kevin

[Tom van Stiphout]

Re: problem with crosstabs + reports

On 5 Feb 2005 06:10:16 -0800, kevcar40@btinte rnet.com (Kevin) wrote:

The first thing that comes to my mind is that in the Report_Open event
you can inspect the Fields collection of the report's underlying
query, and bind/unbind controls accordingly.
In the below code I am assuming that the control names are the same as
the field names, and that initially they are all unbound (i.e. the
ControlSource is empty). Your report would have enough controls for
the maximum number of fields.
There is an alternative of opening the report in design view
(DoCmd.OpenRepo rt "myreport" acDesign) and using CreateReportCon trol
to create controls on the fly.

Private Sub Report_Open(Can cel As Integer)
Dim c As Control
Dim f As DAO.Field
Dim rs As DAO.Recordset
' Open same recordset as the report is using, to get to the fields
collection
Set rs = CurrentDb.OpenR ecordset(Me.Rec ordSource, dbOpenSnapshot)
For Each f In rs.Fields
'Check if this field is also a control. If yes, bind it.
For Each c In Me.Controls
If f.Name = c.Name Then
c.ControlSource = f.Name
Exit For
End If
Next c
Next f
rs.Close
Set rs = Nothing
End Sub

-Tom.

[color=blue]
>Hi
>I have created a report based on a Crosstab query
>the crosstab query is based on another query
>problem i have is that the results this month may be different next
>month
>having tested this by changing the date i find that the field is not
>recongized
>how can i design a report that is dynamic i.e.a field may be there
>this month but not next month
>
>
>thanks
>
>kevin[/color]

[DC]

Re: problem with crosstabs + reports

This is not my solution but check out

http://www.experts-exchange.com/Databases/MS_Access/Q_21018004.html

--
Dean Covey

https://www.coveyaccounting.com

MS-Office Certified:
http://www.microsoft.com/learning/mc...st/default.asp

"Chuck Grimsby" <c.grimsby@worl dnet.att.net.in valid> wrote in message
news:firb01prbt pv6nia96o98i9a5 foqsal2hh@4ax.c om...[color=blue]
> On 5 Feb 2005 06:10:16 -0800, kevcar40@btinte rnet.com (Kevin) wrote:[color=green]
>>I have created a report based on a Crosstab query
>>the crosstab query is based on another query
>>problem i have is that the results this month may be different next
>>month
>>having tested this by changing the date i find that the field is not
>>recongized
>>how can i design a report that is dynamic i.e.a field may be there
>>this month but not next month[/color]
>
> Is the field you're looking for horizontal or vertical?
>
> If it's one of the fields that is horizontal, that is across the top,
> then you can use the IN statement of the PIVOT clause to ensure that
> the column is always there.
>
> If the field is vertical, that is down the side, then use a separate
> query linked back to your crosstab via a LEFT join to ensure the row
> is always there.
>
>
> --
> A Bird In The Hand Will Doo Doo On You.
>[/color]