Show Field from Crosstab Query in Report

[Gordon]

Hello,

I have a report which is based on crosstab query. One of the fields in the query is "date" current range is "Between #7/1/2007# And #7/1/2008#"


Dates are not part of the column or row headings. The date field is there just to limit the range of data which is chosen.

I would like to do two things with this: (1) I would like to show the date range in the report header. However, the "date" field is not a choice in the pull down menu of available fields to include in the report.

(2) I would like the users of the report to be able to choose their own date range, which I have done with other reports by using "between.... and.." statements in the query. "Between [What is First Date] And [What is Final Date]."

I did try puting a text box in the report header, bound to the field in the query as follows: "=qry_rpt_dog_bike_violations]![Date]" but came up with an error message.

Thanks for any help!

 

[pbaldy]

2) http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

1) You can use the parameters in a textbox:

="Data between " & [What is First Date] & " and " & [What is Final Date]

Personally I would use a form to collect the criteria and have the query/textbox point there.

 

[georgedwilkinson]

Be careful using a field/control named "Date" as this is an Access reserved word.

 

[Gordon]

Be careful using a field/control named "Date" as this is an Access reserved word.

Yes Unfortunately, this is a basic field in a table which is used by many queries and reports and I worry that if I change the field name at this point that I will mess up a lot of work which was done "downstream."

Gordon

 

[Gordon]

2) http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

1) You can use the parameters in a textbox:

="Data between " & [What is First Date] & " and " & [What is Final Date]

Personally I would use a form to collect the criteria and have the query/textbox point there.

Thanks.... I tried changing the parameter for the "date" field to a date/time format in the query, per the instructions in the lesson which you linked to above.

The result is that I get a box which requests that I enter a date value. I wonder if this has to do with the problem of the name of the field, "date?"

 

[pbaldy]

What is the SQL of the query now?

 

[Gordon]

What is the SQL of the query now?

Here is the SQL:

TRANSFORM Count(Master.Report) AS CountOfReport
SELECT Master.Violation
FROM Master INNER JOIN Contacts ON Master.[Contact #] = Contacts.[Contact #]
WHERE (((Master.Preserve) Like "fre*" Or (Master.Preserve) Like "*madera*" Or (Master.Preserve) Like "*pur*" Or (Master.Preserve) Like "*windy*" Or (Master.Preserve) Like "*fremont*" Or (Master.Preserve) Like "*bello*" Or (Master.Preserve) Like "*russian*") AND ((Master.date) Between #7/1/2007# And #6/30/2008#) AND ((Master.Report)="c" Or (Master.Report)="w") AND ((Master.Violation) Like "*bicy*" Or (Master.Violation) Like "*dog*"))
GROUP BY Master.Violation, Master.Preserve, Master.date
ORDER BY Master.Violation
PIVOT Master.Preserve;

 

[pbaldy]

The query like that with hard coded parameters throws a parameter box for "date"? You can try bracketing the field name:

Master.[date]

 

[Gordon]

The query like that with hard coded parameters throws a parameter box for "date"? You can try bracketing the field name:

Master.[date]

Thanks Paul,

I placed the [] brackets around "date" and that dealt with the parameter box. However, when I try to use the "between [What is first date] and [What is Last Date]" the query doesn't seem to recognize the format.

The exact error message is "The Microsoft jet database engine does not recognize '[What is first date]' as a valid field name or extension"

Below is the SQL.

Thanks for everyone's time on this!!

PARAMETERS [date] DateTime;
TRANSFORM Count(Master.Report) AS CountOfReport
SELECT Master.Violation
FROM Master INNER JOIN Contacts ON Master.[Contact #] = Contacts.[Contact #]
WHERE (((Master.Preserve) Like "fre*" Or (Master.Preserve) Like "*madera*" Or (Master.Preserve) Like "*pur*" Or (Master.Preserve) Like "*windy*" Or (Master.Preserve) Like "*fremont*" Or (Master.Preserve) Like "*bello*" Or (Master.Preserve) Like "*russian*") AND ((Master.date) Between [What is first date] And [What is second date]) AND ((Master.Report)="c" Or (Master.Report)="w") AND ((Master.Violation) Like "*bicy*" Or (Master.Violation) Like "*dog*"))
GROUP BY Master.Violation, Master.[date], Master.Preserve
ORDER BY Master.Violation
PIVOT Master.Preserve;

 

[pbaldy]

You want the parameter itself in the parameters area, not the field name, plus you need them both. I think it would look like this (as noted, I'd normally use a form):

PARAMETERS [What is first date] DateTime, [What is second date] DateTime;

 

[Gordon]

You want the parameter itself in the parameters area, not the field name, plus you need them both. I think it would look like this (as noted, I'd normally use a form):

PARAMETERS [What is first date] DateTime, [What is second date] DateTime;

That worked and the query is doing exactly what I want it to do!... I also am able to show the chosen date range in the report using a text box with:

="Between " & [What is first date] & " and " & [What is last date]

One odd thing... when I am working on the report in design view the parameter boxes come up in a sort of endless loop... i.e. they continuously want me to enter the starting and ending dates.

Any thoughts on that?

Thanks again for your great advice on this!

Gordon