Crosstab query field in report

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

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.
 
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.



Duane Hookom said:
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.
Click to expand...
Click to expand...
 
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


Bradley C. Hammerstrom said:
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.



Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Duane Hookom said:
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
Click to expand...
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.
Click to expand...
Click to expand...
 
Back
Top