Access 2000 report questions

G

Gale Coleman

Hello.

I have a report based off a crosstab query. The crosstab query lists all
application types which can be either "partial" or "full". The database
right now has only application types "full" in it. On my report I have two
fields, one called full and one called partial that is populated with the
count of application types from the crosstab query. Everything works
beautifully if there are one or more of each application type, if there are
none of one or the other, the report gives me an error.

What I am trying to do is write an IIf statement in the partial and full
field that says if there are no records matching please make this field = 0.
I am trying to write this IIf statement in the control source of the field
on the report. I have read and read on google, but still have not found my
answer. Any suggestions?

Thanks,

gmc
 
A

Allen Browne

Open the crosstab query in design view.
Open the Properties box (View menu.)
Beside the Column Headings property, enter both values, with a comma between
them.
The report now generates a column for both values.

The Partial column will return Null if there are no values.
If you want to convert this to a zero, switch the query to SQL View (View
menu), and add Nz() around the expression in the TRANSFORM clause. For
example, if you see:
TRANSFORM Count(ID) AS CountOfID
change it to:
TRANSFORM Nz(Count(ID),0) AS CountOfID
 
G

Gale Coleman

Absolutely beautiful Allen!!! I knew it had to be something quite easy, I
just couldn't figure it out! Thank you very very much !!!!! Merry Christmas
:)

gmc
 
Top