Crosstab query in Report causes errors

[Gale Coleman]

Hello all,

Using Access 2000 on Windows 2000 pro.

I have a crosstab query based on another query with a parameter of Between
[enter beginning opening date] And [enter ending opening date]. When I run
the crosstab query it comes out just fine. When I put the crosstab query
into a report and run it, it gives me the following error: The Microsoft
Jet Database Engine does not recognize " as a valid field name or
expression.

I have read some things that tell me it is the crosstab query, but I am at a
loss as to what to do to fix it when it gets into the report.

Any suggestions?

Here is the sequel to the crosstab query:

TRANSFORM Count(qMedicarePlanD.Casenum) AS CountOfCasenum
SELECT qMedicarePlanD.ApplicationType, Count(qMedicarePlanD.Casenum) AS
[Total Of Casenum]
FROM qMedicarePlanD
GROUP BY qMedicarePlanD.ApplicationType
PIVOT qMedicarePlanD.Completion;

Here is the sequel to the query qMedicarePlanD:

PARAMETERS [Enter Beginning Opening Date] DateTime, [Enter Ending Opening
Date] DateTime;
SELECT zMedicarePartD.Casenum, zMedicarePartD.Completion,
zMedicarePartD.ApplicationType, zMedicarePartD.Notes
FROM zMedicarePartD INNER JOIN CLIENTSW ON zMedicarePartD.Casenum =
CLIENTSW.CASENUM
WHERE (((CLIENTSW.DOPEN) Between [enter beginning opening date] And [enter
ending opening date]))
GROUP BY zMedicarePartD.Casenum, zMedicarePartD.Completion,
zMedicarePartD.ApplicationType, zMedicarePartD.Notes;

Nothing to complicated, but it really "breaks" the report.

Thanks all,

gmc

 

[Gale Coleman]

Hello all,

I think I figured out part of my problem. It is not the crosstab query, but
the report that is causing me a problem.

In my report I have a field named Full with the control source as "Full"
from the crosstab query.

I have another field named Partial with the control source as "Partial" from
the crosstab query. If the table does not have any records in it named
Partial, then I get the error: The Microsoft Jet Database Engine does not
recognize " as a valid field name or expression.

How do I tell the query (or report) that if there is no record named
"Partial" to put in the number 0 so the report shows 0 in the Partial field?

Thanks all,

gmc

 

[KARL DEWEY]

You can use an IIF statement.

But I think you will still the same error in the crosstab as before unless
you define the parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now strat with TRANSFORM. You need to add like this --
PARAMETERS [enter beginning opening date] Text ( 255 ), [[enter ending
opening date]] Text ( 255 );

 

[Gale Coleman]

Hi Karl,

the dates are not my problem, the problem is that there are no records in
the database that have a name of "Partial" so it gives me that error.

I am trying to find a way to write an IIf statement in the control source of
that field in the report that says the following:

If there are no records in database by the name of "Partial", then make the
value of this field = 0, otherwise use the value from the query.

gmc

 

[Gale Coleman]

Allen Browne supplied me with this:

Open the crosstab query in design view.
Open the Properties box (View menu.)
Beside the Column Headings property, enter both values, with a comma between
them.
The report now generates a column for both values.

The Partial column will return Null if there are no values.
If you want to convert this to a zero, switch the query to SQL View (View
menu), and add Nz() around the expression in the TRANSFORM clause. For
example, if you see:
TRANSFORM Count(ID) AS CountOfID
change it to:
TRANSFORM Nz(Count(ID),0) AS CountOfID


It was exactly what I needed.

gmc