displaying a null value from a query

[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

 

[John W. Vinson]

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

One idea would be to use

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

as your grouping field, rather than Session itself.

 

[Jeff Boyce]

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]

Thanks to both of you: this solution worked perfectly!

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

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

 

[Marshall Barton]

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.

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.