Extra Days in Month in X-Tab Query Report

[yourmomentofzen]

Hi all-

This hopefully is something stupid i'm just not seeing, but i have a
crosstab query report working where the rows are local sites, the
columns are the days of the month (1-31) and the values are rainfall
totals for the day.

the report runs fine for months that have 31 days, however when i try
to implement the report with anything less than 31 days, it gives me
a "The Microsoft Jet Database engine does not recognize " as a valid
field name or expression." i know the problem is the fact that i have
less days that the report presents, so i'm guessing (and hoping)
theres a simple way to fix this without having to make another report
for either 28/29 days and 30 days.

anyone that can help me aleviate this problem is a god-send, since
this is the last issue i have and i'll have a fully functional report.
thanks in advance.

-ymoz-

 

[John Spencer]

In the crosstab query you can specify the field name(s) using an In clause
in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this by
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Inputting your values in Column Headings separated by commas (or
semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

In your case, you would probably be specifying
(1,2,3,4,5,6,7,8,9,...29,30,31)

Depending on how you got the values 1 to 31, you might have to use
("1";"2","3",...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..