Crosstab query field in report

[Bradley C. Hammerstrom]

Access2000

The crosstab query counts inspections. On the report, I want the field to
display "X" if there is at least one inspection, blank for none.

I tried this in the Control Source for a Text Box:
=IIf([Inspection]>0,"X","")
but get #Error

When I leave the Control Source as Inspection it returns the number of
inspections.
Is this do-able?

Brad H.

 

[Duane Hookom]

Try
=IIf(Nz([Inspection],0)>0,"X","")

 

[Bradley C. Hammerstrom]

No good . . .

More detail: There are two similar text boxes; named for the two types of
Inspections--Visual and Test--which are the only two records in the
InspectionType table. The crosstab query has InspectionType as the Column
Heading, and Date as the Value being Counted. (Date of the inspeciton). The
query returns a count of dates each Location (the Row Heading) was
inspected.

This all works fine in a form and in the query result, but for this report I
simply want an X rather than the count.

Tried:
=IIf([Visual]>0,"X","") >>>(returns #Error)

and then:
=IIf(Nz([Visual],0)>0,"X","") >>>(returns #Error)

and even:
=Nz([Visual],0) >>>(returns #Error)

Only this works:
Visual>>>(returns count or blank if none)

The text boxes (one for Visual and one for Test) are in the Detail section
of the report. The report's RecordSource is the crosstab query.
What's going wrong?

Brad H.

Try
=IIf(Nz([Inspection],0)>0,"X","")

--
Duane Hookom
Microsoft Access MVP

Access2000

The crosstab query counts inspections. On the report, I want the field to
display "X" if there is at least one inspection, blank for none.

I tried this in the Control Source for a Text Box:
=IIf([Inspection]>0,"X","")
but get #Error

When I leave the Control Source as Inspection it returns the number of
inspections.
Is this do-able?

Brad H.

 

[Duane Hookom]

Your original posting suggested a field named [Inspection]. Check the Name
of your text box. It can't be the same as the name of the field. Then try:
=IIf(Nz([Visual],0)>0,"X","")

--
Duane Hookom
Microsoft Access MVP

No good . . .

More detail: There are two similar text boxes; named for the two types of
Inspections--Visual and Test--which are the only two records in the
InspectionType table. The crosstab query has InspectionType as the Column
Heading, and Date as the Value being Counted. (Date of the inspeciton). The
query returns a count of dates each Location (the Row Heading) was
inspected.

This all works fine in a form and in the query result, but for this report I
simply want an X rather than the count.

Tried:
=IIf([Visual]>0,"X","") >>>(returns #Error)

and then:
=IIf(Nz([Visual],0)>0,"X","") >>>(returns #Error)

and even:
=Nz([Visual],0) >>>(returns #Error)

Only this works:
Visual>>>(returns count or blank if none)

The text boxes (one for Visual and one for Test) are in the Detail section
of the report. The report's RecordSource is the crosstab query.
What's going wrong?

Brad H.

Try
=IIf(Nz([Inspection],0)>0,"X","")

--
Duane Hookom
Microsoft Access MVP

Access2000

The crosstab query counts inspections. On the report, I want the field to
display "X" if there is at least one inspection, blank for none.

I tried this in the Control Source for a Text Box:
=IIf([Inspection]>0,"X","")
but get #Error

When I leave the Control Source as Inspection it returns the number of
inspections.
Is this do-able?

Brad H.

 

[Bradley C. Hammerstrom]

Ah, yes. I recall reading something like this. . . But get this; I am unable
to rename the text box! I get the error about Microsoft Jet does not
recognize the name as a valid field name or expression. It's just a name for
the text box, why can't I change it? It only works when the Name is Visual
and the ControlSource is Visual which is a field in the crosstab query.

OK, I fixed it. I went and deleted the offending control and dropped a new
text box on the same spot. Keeping the auto-name of Text48, I set the
ControlSource using the IIF(Nz . . . . like before and -- now it works!!
Very odd . . .

Thanks for you help, as always, Duane.

Brad H.

Your original posting suggested a field named [Inspection]. Check the Name
of your text box. It can't be the same as the name of the field. Then try:
=IIf(Nz([Visual],0)>0,"X","")

--
Duane Hookom
Microsoft Access MVP

No good . . .

More detail: There are two similar text boxes; named for the two types of
Inspections--Visual and Test--which are the only two records in the
InspectionType table. The crosstab query has InspectionType as the Column
Heading, and Date as the Value being Counted. (Date of the inspeciton). The
query returns a count of dates each Location (the Row Heading) was
inspected.

This all works fine in a form and in the query result, but for this

report

I
simply want an X rather than the count.

Tried:
=IIf([Visual]>0,"X","") >>>(returns #Error)

and then:
=IIf(Nz([Visual],0)>0,"X","") >>>(returns #Error)

and even:
=Nz([Visual],0) >>>(returns #Error)

Only this works:
Visual>>>(returns count or blank if none)

The text boxes (one for Visual and one for Test) are in the Detail section
of the report. The report's RecordSource is the crosstab query.
What's going wrong?

Brad H.