Report based on Crosstab Query using a form for results

  • Thread starter Thread starter Sshado
  • Start date Start date
S

Sshado

I created a report using a simple crosstab query. I am trying to access the
report using a form where I add dates. I cannot get it to work.

The query is made up of 5 fields:

Field: ShippedBy
Table: tblInput
Total: Group By
Crosstab: Row Heading

Field: DateShipped
Table: tblInput
Total: Group By
Crosstab: Row Heading

Field: ProductType
Table: tblInput
Total: Group By
Crosstab: Column Heading

Field: The Value: SterileLoadNumber
Table: tblInput
Total: Count
Crosstab: Value

Field: Total Of SterileLoadNumber: SterileLoadNumber
Table: tblInput
Total: Count
Crosstab: Row Heading

The Report (named rptShippedBy) basically looks like this:

Products Shipped By Date range: 12/01/2005 -
12/20/2005

Shipped By Product Type 1 Product Type 2
Total

Person 1 2
1 3
Person 2 1
1 2
Person 3 3
4 7

Grand Total 6
6 12

And finally the form (which is where I am having the problem) looks like this:


Choose report [combo box named cboChooseReport]
Dated between [text box named txtBeginDate] and [text box named txtEndDate]
Submit button with the following code:

docmd.gotocontrol "cboChooseReport"

If cboChooseReport.text = "Shipped By" Then

DoCmd.OpenReport "rptShippedBy", acViewPreview, , "(DateShipped >= [Forms]!
[frmBPReports]![txtBeginDate] and DateShipped <= [Forms]![frmBPReports]!
[txtEndDate])"

End If

All my other reports (which are not crosstab based) work with this form.
Anyone know how to fix this?
 
Well, I got it to work. When I looked at the SQL, for some reason when I was
changing the where clause for ShippedDate, it was changing the where clause
for ProductType.
 
Back
Top