Hide text field in report in Access 97 based on field value

[Magnetar]

Afternoon everyone!

I have a quick query in regards to hiding a text box, label, etc, - within the detail section of my report.

On the On Format property, I have the following code:

'If time (hh:mm) does not appear...
If IsNull(Me.txtHours) And IsNull(Me.txtMinutes) Then


Me.txtHours.Visible = False
Me.txtMinutes.Visible = False
Me.lblColon.Visible = False

'Print 'Hours not available this week'...
Me.lblAbsence.Visible = True

Else

Me.txtActualHours.Visible = True
Me.txtActualMinutes.Visible = True
Me.lblColon.Visible = False

End If

I need to check each record using the above code, that appears within the detail section of my report.

Unfortunately, when I activate/open the report, I get the 'lblAbsence' message printed for all records, - not just those for which Me.txtHours and Me.txtMinutes are 'Null'.

Any ideas, anyone?
[blue](I'm still using MS Access 97 for this project, unfortunately!!!)[/blue].

Example of current report output:

01/10/2004 [Hours not available this week]
08/10/2004 [Hours not available this week]
15/10/2004 [Hours not available this week]

....and so on.

Example of report output I wish to achieve(!!):

01/10/2004 -4:32
08/10/2004 [Hours not available this week]
15/10/2004 27:09

....and so on.

Many thanX in advance, 'Guys'!

[highlight]Magnetar[/highlight]

 

[Orion45]

I've run into this problem myself. The issue is that the OnFormat event only runs once when the page is formated and not for each item in your details section. You should try and use the Conditional Formatting to hide the field if it is null. This will evaluate every instance in the details section. Although this tool does not utilize the Visible property I usually just set the font color to white so that the text doesn't show up. This is sort of cheesey but it works. Good luck.

 

[Magnetar]

Dear Orion

Many thanks for your (quick!) response.
I'll let you know how it works out soon.

Kind regards

[Highlight]Magnetar[/Highlight]

 

[MakeItSo]

Hello Magnetar,

You could also try by implementing an alias field in your query containing the value [hours] if non-NULL and "" if Null.
e.g. in query designer:
hrs: IIF(isnull([hours];"";[hours])

P.S: Nice handle "Magnetar" - cosmologist?
;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://andygalambos.de

 

[CosmoKramer]

What you are trying to do should work, I think you are missing a couple lines of code:

If IsNull(Me.txtHours) And IsNull(Me.txtMinutes) Then


        Me.txtHours.Visible = False
        Me.txtMinutes.Visible = False
        Me.lblColon.Visible = False
  
 'Print 'Hours not available this week'...    
        Me.lblAbsence.Visible = True
               
Else

        Me.txtActualHours.Visible = True
        Me.txtActualMinutes.Visible = True
        Me.lblColon.Visible = [highlight]True[/highlight]
        [highlight]Me.lblAbsence.Visible = False[/highlight]
End If

You probably want to see the colon when there is a value, and you have to reset lblAbsence to False when minutes & hours have values.....

Hoc nomen meum verum non est.