getting date range in crosstab query (1 Viewer)

[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

 

[Pat Hartman]

After the crosstab wizard creates the query, you can open it and modify it. Add the date range criteria. You will need to also define your parameters specifically since the crosstab query is very picky.

If your date field really is named "date", now would be a good time to change it. Date is the name of a function and using it as a column name will cause problems in some situations or possiblely invalid results if the current date is used rather than your date field.

Avoid the use of ANY function or property when naming columns. Also do not use embedded spaces or special characters.

 

[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,

 

[Pat Hartman]

With the query open in design view, right click on the background and choose the parameter property. Define [?] as a date.

I suggest something other than ? as the variable name. It may not be valid.

 

[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?

 

[Pat Hartman]

Your problem is that the date in your table is not just a date. It also contains time of day. To use a date such as this as you want to use it, you need to use the DateValue() function

Where DateValue(YourDate) Between [Start Date] And [End Date];

 

[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.