dynamic columns in report

[fredk]

Hi - I am using the dynamic column headings report in the solutions database as a guide - I need to have a report where the number of columns will change - I am on the right track but just need a little help.

In my report open event I have the following code:

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frmReports
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryOverview3_Crosstab")


' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!frmReports!startDate") _
= frm!startdate
qdf.Parameters("Forms!frmReports!EndDate") _
= frm!enddate

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

My problem is that I am getting the following error:

"Undefined function 'sortdate' in expression"

I do have a function in my query that is the source of the report - Do I need to declare it in the qdf? If so, how do I go about this?

Thanks very much for the help!!!!

Fred

 

[chtullu]

Have you defined your parameters in the cross tab query

 

[fredk]

Hi- You got it - that as the problem - !!

Thanks!!!

Fred

 

[dhookom]

I have found the "Solution.mdb" solution to be slow, too much code, and inflexible. I prefer the sample crosstab reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Check the Crosstab.mdb for a solution that allows for almost any number of columns and is much faster.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredk]

I will check that out now - as always, thanks for your help dhookom!


Fred

 

[fredk]

Hi Dhookom - I was looking at the sample database that you linked to - If I may, I have a question - I was looking at the crosstab report database - specifically the rptMultiValueCrosstab report - I have to be honest, I don't yet totally understand what the function (getpart) is doing yet but based on the fields in the report, it seems that this report is hardcoded for just the year 1998.

Am I correct in that and if so, is there a way to change the report so it is not hardcodes for a specific year? Can the recordsource for each field be changed to use a year on a form so the user can select a year? something like:

=GetPart(1,[forms]![mainform]![year] & "01")


Thanks for the help!!!!

Fred

 

[dhookom]

I would use relative months. There are several examples of my solution if you search

http://www.google.com/search?q=hookom+crosstab+Relative+Mth0.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]