Hiding calculated field with null results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple calculated fields whose results return either text from a
memo field or nothing. When the result does not return the text from a memo
field I want the space for that field to close up. The calculation for the
field is done via a Function. Can Shrink and Can Grow are both set to yes for
each field on the report and for the detail section of the report. The fields
are set on the report the way I want them to be when they each return text,
with 1/4" spacing between them vertically. Each field has no other field or
control horizontally equal with it. I have been trying to do the calculation
with an If ... Then ... Else statement , but can't figure out what to put in
the Else section that will return correctly for the function. I've tried
setting the field control Visible to False, but don't think I'm getting the
function to return the information correctly. I've tried setting the function
to return "" as it's string, but that doesn't work. I think I must be missing
something simple, but for the life of me can't figure it out. I appreciate
any help you can give me. Thank you.

My function is written like this, without the else statement:

Function ActivityArrowText() As String

Dim Activity As String
Dim ANum As Boolean
Dim AANum As Byte
Dim AArrowNum As Byte


ANum = False
Activity = ""
AANum = 0
AArrowNum = 0


If ([Forms]![Client input]![1DMonth] = 7 Or [Forms]![Client input]![2DMonth]
= 7 Or _
[Forms]![Client input]![1DDay] = 7 Or [Forms]![Client input]![2DDay] = 7 Or _
[Forms]![Client input]![1DYear] = 7 Or [Forms]![Client input]![2DYear] = 7
Or _
[Forms]![Client input]![3DYear] = 7 Or [Forms]![Client input]![4DYear] = 7)
And _
([Forms]![Client input]![1DMonth] = 8 Or [Forms]![Client input]![2DMonth] =
8 Or _
[Forms]![Client input]![1DDay] = 8 Or [Forms]![Client input]![2DDay] = 8 Or _
[Forms]![Client input]![1DYear] = 8 Or [Forms]![Client input]![2DYear] = 8
Or _
[Forms]![Client input]![3DYear] = 8 Or [Forms]![Client input]![4DYear] = 8)
And _
([Forms]![Client input]![1DMonth] = 9 Or [Forms]![Client input]![2DMonth] =
9 Or _
[Forms]![Client input]![1DDay] = 9 Or [Forms]![Client input]![2DDay] = 9 Or _
[Forms]![Client input]![1DYear] = 9 Or [Forms]![Client input]![2DYear] = 9
Or _
[Forms]![Client input]![3DYear] = 9 Or [Forms]![Client input]![4DYear] = 9)
Then

ANum = True

End If

If ANum = True Then

Randomize ' Initialize random-number generator.

AArrowNum = Int((2 * Rnd) + 1)

Activity = DLookup("[Activity Arrow]", "Numerology Data", "[ID
Number]=" & AArrowNum)
ActivityArrowText = Activity

'Else


End If

End Function
 
A text box will shrink if its value is Null or "" or if it
is invisible. The report section will shrink if a shrinking
control is on a "line" by itself (including an attached
label). If you set the text box's Visible property to
False, the any attached label will also be invisible and the
space will shrink.

If setting the function's return value to "" or making the
text box invisible doesn't work, then look for something
else (e.g. a line control) that's causing the problem.

Another possibility is there may be faulty logic in the
function. You might want to temporarily replace the complex
function with a simple one that just returns a constant
string or "".
--
Marsh
MVP [MS Access]

I have multiple calculated fields whose results return either text from a
memo field or nothing. When the result does not return the text from a memo
field I want the space for that field to close up. The calculation for the
field is done via a Function. Can Shrink and Can Grow are both set to yes for
each field on the report and for the detail section of the report. The fields
are set on the report the way I want them to be when they each return text,
with 1/4" spacing between them vertically. Each field has no other field or
control horizontally equal with it. I have been trying to do the calculation
with an If ... Then ... Else statement , but can't figure out what to put in
the Else section that will return correctly for the function. I've tried
setting the field control Visible to False, but don't think I'm getting the
function to return the information correctly. I've tried setting the function
to return "" as it's string, but that doesn't work. I think I must be missing
something simple, but for the life of me can't figure it out. I appreciate
any help you can give me. Thank you.

My function is written like this, without the else statement:

Function ActivityArrowText() As String

Dim Activity As String
Dim ANum As Boolean
Dim AANum As Byte
Dim AArrowNum As Byte


ANum = False
Activity = ""
AANum = 0
AArrowNum = 0


If ([Forms]![Client input]![1DMonth] = 7 Or [Forms]![Client input]![2DMonth]
= 7 Or _
[Forms]![Client input]![1DDay] = 7 Or [Forms]![Client input]![2DDay] = 7 Or _
[Forms]![Client input]![1DYear] = 7 Or [Forms]![Client input]![2DYear] = 7
Or _
[Forms]![Client input]![3DYear] = 7 Or [Forms]![Client input]![4DYear] = 7)
And _
([Forms]![Client input]![1DMonth] = 8 Or [Forms]![Client input]![2DMonth] =
8 Or _
[Forms]![Client input]![1DDay] = 8 Or [Forms]![Client input]![2DDay] = 8 Or _
[Forms]![Client input]![1DYear] = 8 Or [Forms]![Client input]![2DYear] = 8
Or _
[Forms]![Client input]![3DYear] = 8 Or [Forms]![Client input]![4DYear] = 8)
And _
([Forms]![Client input]![1DMonth] = 9 Or [Forms]![Client input]![2DMonth] =
9 Or _
[Forms]![Client input]![1DDay] = 9 Or [Forms]![Client input]![2DDay] = 9 Or _
[Forms]![Client input]![1DYear] = 9 Or [Forms]![Client input]![2DYear] = 9
Or _
[Forms]![Client input]![3DYear] = 9 Or [Forms]![Client input]![4DYear] = 9)
Then

ANum = True

End If

If ANum = True Then

Randomize ' Initialize random-number generator.

AArrowNum = Int((2 * Rnd) + 1)

Activity = DLookup("[Activity Arrow]", "Numerology Data", "[ID
Number]=" & AArrowNum)
ActivityArrowText = Activity

'Else


End If

End Function
Click to expand...
 
Thanks. After much trial and error I figured out that it was the space I was
leaving on the report itself that was causing the problem, not the field. The
field was working correctly. That's what happens when you try to help a
friend out after being away from databases and coding for over 15 years.
 
Back
Top