By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

problem with crosstabs + reports

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 5 Feb 2005 06:10:16 -0800, ke******@btinternet.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.OpenReport "myreport" acDesign) and using CreateReportControl
to create controls on the fly.

Private Sub Report_Open(Cancel 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.OpenRecordset(Me.RecordSource, 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.

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


Nov 13 '05 #2

P: n/a
DC
This is not my solution but check out
http://www.experts-exchange.com/Data..._21018004.html
--
Dean Covey
www.coveyaccounting.com

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

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:fi********************************@4ax.com...
On 5 Feb 2005 06:10:16 -0800, ke******@btinternet.com (Kevin) wrote:
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


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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.