The column headings property is set to the results of a Union query. No I'm
not handling the dynamic columns in the report because I don't know how (that
was going to be another post probably). Guessing the lack of dynamic columns
is part of the problem? Below is the SQL for the crosstab (sorry its so
ugly).
PARAMETERS [Forms]![Site List menu]![Field_no] Text ( 255 );
TRANSFORM Sum(qry_ArtifactUnion.Count) AS SumOfCount
SELECT TRU.Northing, TRU.Easting, Concatenate("SELECT Field_Number FROM
Features WHERE TRU_Northing =" & [Northing] & " And TRU_Easting =" &
[Easting]) AS Features, First(TRU.FCR_Count) AS FCR, First(Comment.Comment)
AS Comment
FROM ((Site_boundary RIGHT JOIN ((TRU LEFT JOIN qry_ArtifactUnion ON
(TRU.Easting = qry_ArtifactUnion.TRU_Easting) AND (TRU.Northing =
qry_ArtifactUnion.TRU_Northing)) LEFT JOIN lnk_TruSite ON (TRU.Easting =
lnk_TruSite.TRU_Easting) AND (TRU.Northing = lnk_TruSite.TRU_Northing)) ON
Site_boundary.Id = lnk_TruSite.SiteID) LEFT JOIN Comment ON (TRU.Date =
Comment.Date) AND (TRU.Initials = Comment.Initials) AND (TRU.Easting =
Comment.TRU_Easting) AND (TRU.Northing = Comment.TRU_Northing)) LEFT JOIN
Features ON (TRU.Date = Features.Date) AND (TRU.Initials = Features.Initials)
AND (TRU.Easting = Features.TRU_Easting) AND (TRU.Northing =
Features.TRU_Northing)
WHERE (((Site_boundary.Field_no)=[Forms]![Site List menu]![Field_no]))
GROUP BY TRU.Northing, TRU.Easting
PIVOT qry_ArtifactUnion.Type;
:
We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm
:
Hi All,
I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).
The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!
Thanks