Null Values

[David]

Hi,

I am using a software called CodeBook which is run via an access database.
This database stores information on hospital rooms, and part of this is all
the equipment within each room.

What I am looking for is when a room has no equipment, how do I get access
to place a piece of text on the report sheet saying 'no equipment found in
room'.

Regards,
Dave

 

[Jeff Boyce]

Dave

"How" depends on "what" ... more specific descriptions may lead to more
specific suggestions.

However, one possibility is that you could use a query to get the records
you'll want to print for each room, then add an IIF() statement to generate
the "No equipment" message for Null in (?!)some field.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[David]

Jeff,

Thanks for the information, apologies for the lack of information as new to
some of this.

I have a query where one of the fields is 'cwfCode2' generated from a table
called 'CWT_LIB_Equip_and_Unions'. I have placed this field within the report
which runs fine showing the equipment, but when it comes to a room with no
equipment it reports a blank page.

I tried to use the 'IIf' function in the query which then generates a new
field, but this did not work, and I also tried to use the 'IIf' function in
the report where I placed an expression against the 'cwfCode2' field, but
this did not work either.

The query and the report both run fine, but when I try and tell any empty
rooms (cwfCode2) to report as text, either no text is reported, or no
equipment reports at all in any rooms, as I still require the rooms with
equipment to report correctly.

Any help/advice would be much appreciated.

 

[Armen Stein]

I tried to use the 'IIf' function in the query which then generates a new
field, but this did not work, and I also tried to use the 'IIf' function in
the report where I placed an expression against the 'cwfCode2' field, but
this did not work either.

It's hard to know from your description what "did not work" means.

In your report's control's controlsource, try something like:

=IIF(IsNull(cwfCode2),"<No equipment found in room>",cwfCode2)

That should work if it really is a Null. If it might be Null or an
empty string, this is more robust and will handle it either way:

=IIF(cwfCode2 & "" = "","<No equipment found in room>",cwfCode2)

Remember that if the report's control is also named cwfCode2, you'll
need to rename it (suggest to txtcwfCode2), otherwise you'll get a
naming error.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com