Crosstab question....

[Guest]

I have a crosstab question.

I have a form based off a Crosstab query. It all works well, except that
until
all the data is populated the crosstab query doesn't populate all possible
results. Is there a way to see if a query returns a field? I need to make a
calculation based on the whether the field data exists, but if the crosstab
doesn't return that field, I want that to reflect in the calculation.

Thanks.

 

[Guest]

Joe,

Not sure whether this is what you are looking for, but you can force the
crosstab to return all the columns you are looking for, if you know what they
will be in advance. For example, if you know you want columns for each of
the months, and you have not entered any data yet for May, you could still
force it to return a column for May, with NULL values.

The way to do this is to click in the gray portion of the query grid, and
display the properties of the query. In the area for "Column Headings",
enter the values you expect your cross-tab query to return for the column
headers. Beware, these values must exactly match the values that the
crosstab generates for column headers or your data will not be displayed.

I thnk, once you have these columns in your crosstab, you can test for NULL
values and use that data appropriately.


HTH
Dale

 

[Guest]

Dale,

Thanks for the reply. This partially answers what I wanted. I was hoping for
something a little more flexible where if something changes in the future that
I won't have to change the query.

If the end user adds something outside of that, then it won't show up. I
guess
that I can run code that will populate that field with all available fields at
startup and that would give me the flexibilty.

You;ve taught me something. Thanks for that.

Joe