criteria for cross tab report

[Michel Peeters]

I have a crosstabreport "rptWeek" which shows the weekly sales and from
which the fiels are looking more or less as follows:

Group By - Row Heading:
CustomerId
ProductId
ProductName
ProductPrice
.....

Group by - Column Heading:

Expr1: "W" & DateDiff("ww";[SalesDate];Forms!fdlgRptWeek!txtBeginSalesDate)



Expression - Value

The Value: Sum([Salesvalue])



Where

SalesDate

Between [Forms]![fdlgRptWeek]![EindSalesDate] And
[Forms]![fdlgRptWeek]![BeginSalesDate]





The user enters the criteria (customerId, ProductId etc...) in the dialog
form fdlgRptWeek.

Then I open the report with Docmd.Openreport....strWhere.

strWhere specifies the Where condition

Everything works.



For some reasons I want also "[SalesDate] Between
[Forms]![fdlgRptWeek]![EindSalesDate] And
[Forms]![fdlgRptWeek]![BeginSalesDate]" in the Where condition instead off
in the query raster from the report.



If I put [SalesDate] in the queryraster as Group by - Columnheading - this
will not work because then my report has a seperate line for every day.

If I put [SalesDate] - Where - in the query raster [SalesDate] will not be a
field in the report and will not be accepted as criteria.

How can I solve this?



tks - Michel

 

[Duane Hookom]

If your query doesn't return a date field as a row heading, you can't use
dates in the Where Clause of DoCmd.OpenReport. You must either modify the
SQL property of the saved query or use Between
Forms!fdlgRptWeek!txtBeginSalesDate And .... or use the Column Headings
property to limit the dates returned.

You must declare your parameter data types.