To be NULL or Not To be NULL Help!!!!!!!!!

[mmasuda]

I am trying to use an unbound text box in a report to display comments from a query. Sometimes there are no comments to display. In that case I want to display the message "No Comments were made".

First I used this:
IIf(IsNull([not_cur_there]), "No Comments were made", [not_cur_there)
When there were comments, they were returned but when there weren't any, nothing was returned at all. I looked closer at my query and began to suspect that the values were not NULLs but zero-length strings.

After some searching I found this statement:
IIf(IsNull([not_cur_there]), "No Comments were made", Foramt([not_cur_there], "@;\No Comments were made")). This is supposed to return "No Comments were made" if the field is NULL or zero-length. I, however, get this:
#Error

I add this to the detail section of the report. If I add the text box "not_cur_there". Then it will return "no comments were made." But if there are comments, then it will return everything in duplicate.
I am at the end of my wits.
Please, help

 

[mwn1218]

One thing you can do so it does not return the duploicate ones is:

put a text box call txtCount in the detail section and set the control source of it to =1 and set the running sum property to Over Group and visible to false.

Then in the on format event of the detail section add

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtCount.Value = 1 Then
Me.no_cur_there.Visible = True
ElseIf Me.txtCount.Value > 1 Then
Me.no_cur_there.Visible = False
End If
End Sub

where no_cur_there is the name of the text box.

This will only show it for the first record

 

[mmasuda]

I tried to follow this and I understand what it is supposed to do, however, I get run-time erroe 2427, "you entered an expression that has no value." It is referring to txtCount. I did set the control source to =1.
Anyway I can bypass this problem?

 

[BobStubbs]

Try this 'cheat', which tests whether the text box contents is greater than a single space or not ...

IIf([not_cur_there]>" ", [not_cur_there], "No comments were made")

I hope that this helps.



Bob Stubbs