IIF statement in Report - Access 2000

[Gale Coleman]

Hello all,

Using Access 2000 and windows 2000 professional

I have a report that is based on a crosstab query that is based on another
query. I have a field on the report that sometimes does not have a record
on the database for it. When that happens I get the following error:

The Microsoft Jet Database Engine does not recognize " as a valid field name
or expression.

How can I write an IIF statement in the control source of the report to tell
the text field to be the value of 0 if there are no records for it?

Thanks,

gmc

 

[BruceM]

Do you mean that if there is no value in the field, you want the text box
bound to that field to display a zero? If so, you could set the default
value of the field to "0".

 

[Gale Coleman]

Nope, I meant if there is no record in the table with an application type
named "Partial", then make an IIF statement on the report that states if
there is no record in the table with an application type named "Partial"
that a default value for that text field on the report is 0.

The report calls a crosstab query that shows the fields. If there was a
record with Partial as the name of an application type then it would show
how many there are with that application type. Since there are no records
in the table with an application type like "Partial" then it gives me the
error.

gmc



"BruceM" wrote in message

Do you mean that if there is no value in the field, you want the text box
bound to that field to display a zero? If so, you could set the default
value of the field to "0".

 

[Gale Coleman]

A little more clarification maybe?

I have a field on my report named PartialRecord, the report is based on a
crosstab query. If there is a value for the field PartialRecord it would
say "Partial" in the field. Since sometimes there is no value for that
field how would I write the statement so it says this:

If there is no value for the field named PartialRecord, make the value of
the field the number 0 otherwise make the value of the field what the query
produced.

I am trying to do this through the control source on the report for the
PartialRecord field.

gmc

 

[BruceM]

Haven't been at my computer very much lately, thus the delay responding.
Tables and queries contain fields. Reports and forms contain controls such
as text boxes that can be bound to the field (the field is the control's
Control Source). Is PartialRecord a field in a table, or is it a field you
have constructed in the query alone? If it is in the table, what type of
field is it: Text, Number, Yes/No?
Remember that I can't see your database, so I don't know much about the
table on which the query is based, or the query on which the report is
based. For now I will assume the query is based on a table named tblMain,
and that the table contains a text field named ApplicationType. The IIf
statement, which could be placed in the control's Control Source, may be
something like:
=IIf([ApplicationType] = "Partial","Partial","0")
For that to work, ApplicationType needs to be a field in the query.

 

[Gale Coleman]

Hi Bruce,

Allen Browne gave me exactly what I needed:

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