IIF IS NULL questionnn

[Guest]

I have a form which a user can scroll through a listing in a list box. The
user can select a record and click a print preview button to open a report
that is based on a query, showing info on their selection. On that report, I
want to let the user know if there is nothing to report.

On a control, the source is:

=IIf(IsNull([classname]),"No Reviews",[classname])

this returns "#Error" when I think it should be returning "No Reviews".

What am I missing here?

tia,

 

[John Conklin]

I use the following on any report that I have that doesn't contain any data
for the user to see.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "You selected no data to print.", vbInformation
Cancel = True
End Sub

Add this to the On No Data event of the report.

It works well, and then if there is no data for the report it shows a
message box instead of a blank report.

~John

 

[Guest]

thanks for the reply.

That works when there is nothing returned from the query. If there are null
fields brought back from the query, I would still want to not let the user
think there is nothing there. If a person doesn't fill in a field, it should
say "N/A", or something to that effect. With the iif statement I have, it
returns an "#Error" instead. Shouldn't I be able to configure that iif
statement to give me that?

tia,
JMorrell

I use the following on any report that I have that doesn't contain any data
for the user to see.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "You selected no data to print.", vbInformation
Cancel = True
End Sub

Add this to the On No Data event of the report.

It works well, and then if there is no data for the report it shows a
message box instead of a blank report.

~John

I have a form which a user can scroll through a listing in a list box. The
user can select a record and click a print preview button to open a report
that is based on a query, showing info on their selection. On that report, I
want to let the user know if there is nothing to report.

On a control, the source is:

=IIf(IsNull([classname]),"No Reviews",[classname])

this returns "#Error" when I think it should be returning "No Reviews".

What am I missing here?

tia,

 

[Steve Schapel]

JMorrell,

John Conklin's suggestion is a good one.

The reason your approach does not work is because if there is no data,
[classname] will not be Null. It doesn't exist, so it can't be Null or
anything else.

As an aside, in any case your expression could be simplified to...
=Nz([classname],"No Reviews")

However, if you want the report to display/print, with your notification
on it, you can use this in the Control Source of your textbox instead...
=IIf([Report].HasData,Null,"No Reviews")

 

[John Vinson]

With the iif statement I have, it
returns an "#Error" instead. Shouldn't I be able to configure that iif
statement to give me that?

Yes: use

NZ([fieldname], "N/A")

as the control source for the textbox or as a calculated field in the
query.

John W. Vinson[MVP]

 

[Steve Schapel]

JMorrell,

It really depends on whether we are talking about a report where there
are no records, or a report where there are records but some of the
fields in the record are empty.

 

[Steve Schapel]

.... but here's another thought: If you have an expression such as
=Nz([classname],"No Reviews") as the Control Source of a textbox, then
it is important that the name of this textbox is not the same as the
name of a field in the record source of the report.

 

[Guest]

Yes. I think I have that part covered.

Thanks for all your help!

JMorrell

.... but here's another thought: If you have an expression such as
=Nz([classname],"No Reviews") as the Control Source of a textbox, then
it is important that the name of this textbox is not the same as the
name of a field in the record source of the report.

--
Steve Schapel, Microsoft Access MVP

JMorrell,

It really depends on whether we are talking about a report where there
are no records, or a report where there are records but some of the
fields in the record are empty.