I am trying to do a cross tab Report, and having great difficulty. I have made a cross tab query with Row Headings of SubGroupID, and Column Heading of Date, Value of “Count” that is Summed. So I should the list of SubGroups going down and the Dates going across. I have tried working with the example in the Solutions Database and I get this error: “The expression On Open you entered as the event property setting produced the following error: User-defined type not defined.” I remarked out the code relating to the Form “EmployeeSalesDialogBox” and the Parameters since my query does not have parameters.
As an alternative to the above solution, I used the wizard to create a cross tab report. This set the text boxes in the detail section to specific dates (four dates for four columns), and the report works fine. The Problems is that the date will be changing every time I run the report (Once a week) and we are trying to automate the report. However I know what the date are (current date, current date –7, ext). So I want to use VB to make the control source equal to the field in my query(current date, and current date –7, ext). I have tried [Me.Date1.ControlSource = "=date()"] in the on Open event for the report, and it makes the value of the box current date(6/15/01) not the sum of “Count” for date 6/15/01. I have also tried [Me.Date1.ControlSource = "date()"], and [Me.Date1.ControlSource = "(date())"], and they both give me an error “The Microsoft Jet database engine does not recognize’[date()]’ as a valid field name or expression.” Please point me in the right direction, I would prefer the second solution because it seems easier, but getting it working is more important. Thanks in advance for your assistance.
As an alternative to the above solution, I used the wizard to create a cross tab report. This set the text boxes in the detail section to specific dates (four dates for four columns), and the report works fine. The Problems is that the date will be changing every time I run the report (Once a week) and we are trying to automate the report. However I know what the date are (current date, current date –7, ext). So I want to use VB to make the control source equal to the field in my query(current date, and current date –7, ext). I have tried [Me.Date1.ControlSource = "=date()"] in the on Open event for the report, and it makes the value of the box current date(6/15/01) not the sum of “Count” for date 6/15/01. I have also tried [Me.Date1.ControlSource = "date()"], and [Me.Date1.ControlSource = "(date())"], and they both give me an error “The Microsoft Jet database engine does not recognize’[date()]’ as a valid field name or expression.” Please point me in the right direction, I would prefer the second solution because it seems easier, but getting it working is more important. Thanks in advance for your assistance.
