return zero if field missing on crosstab

  • Thread starter Thread starter Wylie
  • Start date Start date
W

Wylie

I'm using Access 2000, trying to get a report to display from a
crosstab query - a trial balance to be specific. However, if nothing
is in the 31-60 aging range the field is not generated in the crosstab
query, and therefore causes on error when the report tries to reference
the field in the report. My thought was there must be something
similar to the NZ function to return a value when the referenced field
is not in the query.
 
Wylie said:
I'm using Access 2000, trying to get a report to display from a
crosstab query - a trial balance to be specific. However, if nothing
is in the 31-60 aging range the field is not generated in the crosstab
query, and therefore causes on error when the report tries to reference
the field in the report. My thought was there must be something
similar to the NZ function to return a value when the referenced field
is not in the query.
Click to expand...

One way I have gotten around that problem in the past with crosstabs is:

....
SELECT IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0) As Balance31_60
....
GROUP BY IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0)
....

I just used the first thing that worked so there may be better ways.

James A. Fortune
(e-mail address removed)
 
James said:
One way I have gotten around that problem in the past with crosstabs is:

...
SELECT IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0) As Balance31_60
...
GROUP BY IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0)
...

I just used the first thing that worked so there may be better ways.

James A. Fortune
(e-mail address removed)
Click to expand...

I'm not quite there. I created a query on my crosstab, included

Past Due 31-60: IIf([Past 31-60] Is Not Null,[Past 31-60],0)

to handle the field [Past 31-60] if it is missing from the crosstab,
and I stiil get the "The Microsoft Jet database engine does not
recognize '[Past 31-60]' as a valid field name or expression"

-Wylie
 
You can Also Use The In Predicate

TRANSFORM ...
SELECT ...
PIVOT Range In
('AgingRange0_15','AgingRange16_30','AgingRange31_45','AgingRange46_60','AgingRange60+')

Will return a Null For non-existing data

HTH

Pieter



Wylie said:
One way I have gotten around that problem in the past with crosstabs is:

...
SELECT IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0) As
Balance31_60
...
GROUP BY IIf(AgingRange31_60 IS NOT NULL, AgingRange31_60, 0)
...

I just used the first thing that worked so there may be better ways.

James A. Fortune
(e-mail address removed)
Click to expand...

I'm not quite there. I created a query on my crosstab, included

Past Due 31-60: IIf([Past 31-60] Is Not Null,[Past 31-60],0)

to handle the field [Past 31-60] if it is missing from the crosstab,
and I stiil get the "The Microsoft Jet database engine does not
recognize '[Past 31-60]' as a valid field name or expression"

-Wylie
Click to expand...
 
Pieter said:
You can Also Use The In Predicate

TRANSFORM ...
SELECT ...
PIVOT Range In
('AgingRange0_15','AgingRange16_30','AgingRange31_45','AgingRange46_60','AgingRange60+')

Will return a Null For non-existing data

HTH

Pieter
Click to expand...

Pieter,

It worked. You're a genius.

Thank you,

-wylie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Date Range in Reports 3
Null to Zero in Crosstab Query 1
Crosstab Null value display as a "0" 2
Is it possible to get Access to ignore not recognizing a field nameusing some sort of error handling 1
Form based on crosstab query 1
Basing Report on Crosstab 3
problem with crosstab query 1
cannot use the crosstab of a non-fixed column as a subquery 3

Back
Top