displaying a null value from a query

  • Thread starter Thread starter Fredrated
  • Start date Start date
F

Fredrated

I am creating a report based on a summary query where one of the 'group by'
fields will sometimes be blank, thus there will be one line in the report
that has an empty field. How do I get something like 'Option not picked' to
appear in the blank spot instead of just a blank?
I tried putting =IIF(isnull([session]),"Option not taken",[session]) into
the control source to replace the field session, but I get #ERROR as a result.

Any ideas?

Thanks

Fred
 
I am creating a report based on a summary query where one of the 'group by'
fields will sometimes be blank, thus there will be one line in the report
that has an empty field. How do I get something like 'Option not picked' to
appear in the blank spot instead of just a blank?
I tried putting =IIF(isnull([session]),"Option not taken",[session]) into
the control source to replace the field session, but I get #ERROR as a result.

Any ideas?

Thanks

Fred
Click to expand...

One idea would be to use

NZ([Session], "Option not taken")

as your grouping field, rather than Session itself.
 
Fred

As an alternate approach, consider using something like the following in
your query (untested):

YourNewField: Nz([session],"Option not taken")

Be aware that to humans, a blank, a null, and a space all look the same in a
control/field... but not to Access!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks to both of you: this solution worked perfectly!

Jeff Boyce said:
Fred

As an alternate approach, consider using something like the following in
your query (untested):

YourNewField: Nz([session],"Option not taken")

Be aware that to humans, a blank, a null, and a space all look the same in a
control/field... but not to Access!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Fredrated said:
I am creating a report based on a summary query where one of the 'group by'
fields will sometimes be blank, thus there will be one line in the report
that has an empty field. How do I get something like 'Option not picked'
to
appear in the blank spot instead of just a blank?
I tried putting =IIF(isnull([session]),"Option not taken",[session]) into
the control source to replace the field session, but I get #ERROR as a
result.

Any ideas?

Thanks

Fred
Click to expand...
Click to expand...
 
Fredrated said:
I am creating a report based on a summary query where one of the 'group by'
fields will sometimes be blank, thus there will be one line in the report
that has an empty field. How do I get something like 'Option not picked' to
appear in the blank spot instead of just a blank?
I tried putting =IIF(isnull([session]),"Option not taken",[session]) into
the control source to replace the field session, but I get #ERROR as a result.
Click to expand...

A common reason for #Error in that situation is that the
text box is still named Session. Try changing its name to
something else such as txtSession.
 
Back
Top