Crosstab query for Aged Analysis Error

[nclsgna via AccessMonster.com]

I have a report that is based on a crosstab query. The query produces an
aging analysis of debtors as follows:

Customer: Balance: 0-14 15-29 30-44 45+
-------- -------- ---- ---- ---- ----
A 50 25 20 5
B 100 10 50 40

Crosstab query on its own is working fine, but the problem comes on the
report. In certain periods there are no debtors balances older than 44 days
and on such periods the report returns an error.

Is there a way of validating the non-extant period to zero when there is no
data?

 

[Guest]

What is probably happenins that if there is no data for a certain range in a
crosstab, Access does not create that field in the query. The report is
looking for it as the record source for a field. Therefore it errors.

Look into the Column Headings property of the crosstab query. With the
query in design view, right click on the gray area near the tables and select
Properties.
It would look something like below:
"0-14","15-29","30-44","45+"

That way there will be a 45+ even if there is no data in it. If for some
reason you don't want to see 0-14, just remove it from the column headings
and it won't show up.

 

[nclsgna via AccessMonster.com]

I did as you suggested, and the 45+ days category is now showing with null
values; however the 0:14 category is no longer showing the values. Can you
assist?

What is probably happenins that if there is no data for a certain range in a
crosstab, Access does not create that field in the query. The report is
looking for it as the record source for a field. Therefore it errors.

Look into the Column Headings property of the crosstab query. With the
query in design view, right click on the gray area near the tables and select
Properties.
It would look something like below:
"0-14","15-29","30-44","45+"

That way there will be a 45+ even if there is no data in it. If for some
reason you don't want to see 0-14, just remove it from the column headings
and it won't show up.

I have a report that is based on a crosstab query. The query produces an
aging analysis of debtors as follows:

[quoted text clipped - 10 lines]

Is there a way of validating the non-extant period to zero when there is no
data?

 

[nclsgna via AccessMonster.com]

I did as you suggested, and in the query and the report the 45+ days category
is now showing with null
values; the report is however resulting in a Null for the 0:14 category. Am I
missing something?

What is probably happenins that if there is no data for a certain range in a
crosstab, Access does not create that field in the query. The report is

[quoted text clipped - 14 lines]

 

[John Spencer]

The values you put into the in clause MUST be exactly the same as those that
are generated by the query. So I would check the names.

For instance, you referred to 0-14 in your original post and now you are
referring to 0:14. Check for leading spaces and spaces between the
separator (- or and the range values.

I did as you suggested, and in the query and the report the 45+ days
category
is now showing with null
values; the report is however resulting in a Null for the 0:14 category.
Am I
missing something?

What is probably happenins that if there is no data for a certain range
in a
crosstab, Access does not create that field in the query. The report is

[quoted text clipped - 14 lines]

Is there a way of validating the non-extant period to zero when there
is no
data?