getting date range in crosstab query

[mech55]

My question involves the table below

AutoNumber(primary key)
SerialNumber
Station
Defect Type
Date

Basically now I need to be able to get the result i've gotten by a date range, but the crosstab query won't let me do it the way you can in a regular select query...help..

------------------Station 1 ----------Station 2------------Station 3---------------Station4
RxFailure ------------52-----------------52
Bit Error Rate 10 -----0------------------15

 

[mech55]

yeh, I did change the date field however problem is the same this is the access generated sql

TRANSFORM Count(Defect_Log.[Defect type]) AS [CountOfDefect type]
SELECT Defect_Log.[Defect type]
FROM Defect_Log
WHERE (((Defect_Log.Defect_Date)=[?]))
GROUP BY Defect_Log.[Defect type], Defect_Log.Defect_Date
PIVOT Defect_Log.Station;

when I try to go into view mode I get 'Microsoft engine does not recognize [?] as a valid field name or expression. And i'm not sure what you mean by specify date parameters specifically. Could you please elaborate.

Thanks,

 

[mech55]

the variable name is not [?]. this was what I was using as a criteria, but it could very well be something like

between [Start Date] and [End Date]

With this I get a message saying 'Microsoft engine does not recognize [Start Date] as a valid field name or expression.'

see what I mean?

 

[mech55]

I FIGURED IT OUT, WEEE,

I have to create the parameter, but then I also have to go down and enter it below the date field. that's what i was missing, thanks.

 

[sara82]

mech55

Can you please tell me what you did? I have the same problem. With me I added it to the parameters and also to the Date field but it is not filtering out to the correct dates. For instance when the parameter comes up for StartDate I enter 10/1/05 and parameter for EndDate I enter 10/15/05. When I run the query it shos me dates 10/1/05 and 10/14/05. It seems it's not including the end date

What did you put in the Date field?

 

[Sara Delate]

creating a report from a crosstab query with parameters

I have followed your discussion on crosstab queries with parameters and it helped me out too.

But now I would like to create a report based on my crosstab query and I have a problem: when selecting the query in the Report Assistant, the Assistant does not propose any columns.

When I try to create my report in Creation mode, the parameters' boxes (I have two) keep popping up and reclaiming parameter values. In the end, Access closes down by itself.

My query is as follows:
PARAMETERS pannee Short, pmois Short;
TRANSFORM Count(plcmt_jour.ND_adsl) AS CompteDeND_adsl
SELECT plcmt_jour.offre, plcmt_jour.partenaire, plcmt_jour.mois, Count(plcmt_jour.ND_adsl) AS [Total de ND_adsl]
FROM plcmt_jour
GROUP BY plcmt_jour.offre, plcmt_jour.partenaire, plcmt_jour.mois
PIVOT plcmt_jour.jour;

This works fine as a query, but how do I get it into a report???

Please help!
(another) Sara

 

[Rich]

You should pass the Parameters to the query via textboxes on a form, try opening the query first and then creating the Report, keep the form open when opening the Report

 

[JoeCruse]

If you want the report to have columns, you will either have to make a dynamic crosstab report (do a search on this site for this) or define column headings in your crosstab query. Without doing this, Access does not have any info to suggest columns for your report. If you are going to have a static set of columns, then it's best to set up column headings in the query. If they are not static, and there are many possible combinations of columns that the query may output, your best bet is to do a dynamic crosstab report, based on the query. It isn't easy, but it DOES work.