Crosstab Query - Report

[Guest]

Hello,

I have a crosstab query that runs fine and I get the information I need but...
when I try to run a report from it I get the error "The Microsoft Jet
database engine does not recognize 'Alldoctotals2004.refdoctype' as a valid
field name or expression.

I have read the other post on this but I guess I am not following... I
don't want it as a parameter so how do I get it to print like the query runs?

Query:SQL view

TRANSFORM Sum(AllDoctorTotals2004.refcnt) AS SumOfrefcnt
SELECT AllDoctorTotals2004.REFDOC_NAME, AllDoctorTotals2004.spc_descrip
FROM AllDoctorTotals2004
GROUP BY AllDoctorTotals2004.REFDOC_NAME, AllDoctorTotals2004.spc_descrip
PIVOT AllDoctorTotals2004.refdoctype;


Thanks for the help...

Julia

 

[Steve Schapel]

Julia,

Is it a typo in your post here, or does the error message really refer
to Alldoctotals2004.refdoctype, whereas your query contains
AllDoctorTotals2004.refdoctype? Does this mean that you have a control
on your report, or code, referring to a non-existent field?

 

[Guest]

Steve,

Sorry that is my typo --- The error message does say
AllDoctorTotals2004.refdoctype.

Julia

 

[Steve Schapel]

Julia,

The crosstab query returns a field/column for each refdoctype value.
The controls on your report need to source these columns in the query,
not the refdoctype field directly. For example, if you have three
refdoctypes, "Something", "Something Else", and "Mugwump", then your
report needs to be designed with three controls (textboxes) for
"Something", "Something Else", and "Mugwump", i.e. bound to the
[Something], [Something Else], and [Mugwump] fields in the report's
underlying record source, i.e. the crosstab query.

 

[Guest]

The report does have the three (it just happens to be three columns) in
texboxes but I still get the error.

any other ideas?

Thanks again for your help!!

Julia,

The crosstab query returns a field/column for each refdoctype value.
The controls on your report need to source these columns in the query,
not the refdoctype field directly. For example, if you have three
refdoctypes, "Something", "Something Else", and "Mugwump", then your
report needs to be designed with three controls (textboxes) for
"Something", "Something Else", and "Mugwump", i.e. bound to the
[Something], [Something Else], and [Mugwump] fields in the report's
underlying record source, i.e. the crosstab query.

--
Steve Schapel, Microsoft Access MVP

Steve,

Sorry that is my typo --- The error message does say
AllDoctorTotals2004.refdoctype.

Julia

 

[Steve Schapel]

Julia,

It seems to me that the refdoctype field is somehow being referenced
somewhere within your report design, either in a control's control
source, or in VBA code module, or in the Sorting & Grouping dialog, or
somewhere. Which is not valid because this field does not exist in the
query. All I can suggest is that you poke around until you find it.

 

[Guest]

I did poke around and YOU WERE CORRECT it was referenced and when I deleted
it ..... It worked!!!

Thank You...Thank You...Thank You!!!!!!

 

[Steve Schapel]

Julia,

Very happy to hear of your success