Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    NZ() function on report (XP)

    I have a field on a report that I want to be invisible if there's nothing to put in it. Specifically, this item appears as a text box that displays the following:

    "Register student in " & [JSEP_Program]

    So I've built an NZ() function into the query. The field reads: Rev: Nz([JSEP_Program],"No JSEP program"). And it works: With the query in Datasheet view, where a given student has no JSEP program, the field displays "No JSEP program." That's cool. It's my very first NZ() function, and I'm pleased with myself. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> But...now what?

    What I really want is for that text box to be invisible if the field [JSEP_Program] is Null. I tried writing an expression in Conditional Formatting:

    IF IsNull [JSEP_Program], Visible = False

    but it appears I've left something out. So I tried writing a VBA procedure:

    Private Sub JSEP_Visible()
    If Me![JSEP_Program] = Null Then
    Me![Label82].Visible = False
    End If
    End Sub

    I didn't get any error messages, but it didn't make the text box invisible, either. Can someone help me tweak this, please. Thanks!

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: NZ() function on report (XP)

    I think 1 simple line of code in your the Format event of your report's Detail section should do the trick.

    txtJSEP_Program.visible = not isnull(JSEP_Program)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: NZ() function on report (XP)

    Thank you for the quick reply, Mark. I'd left the Name property for this text box at the default: "Label82," so I modified your line of code to read,

    Label82.visible = not isnull(JSEP_Program)

    When I tried to run the code (that is, when I launched the report), I got an error message telling me that Access can't find the field JSEP_Program. Well, that seems odd

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ() function on report (XP)

    The code should be in the PageHeader_Format and not in the Detail_Format.
    Another thing : did you name the field in the query : REV ?
    Then the line would be :
    Label82.visible = NOT(rev ="No JSEP program.")
    Francois

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: NZ() function on report (XP)

    Well for one thing, you would have to move the code to the Report Section's Format event.

    As for Access' complaint about the field name, I can only assume either that there is a spelling inconsistency somewhere, or that you didn't include this field in the query behind the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: NZ() function on report (XP)

    Thank you, Fran

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: NZ() function on report (XP)

    Hello, Mark, and thanks again. The problem's fixed. I presumed that, since the concatenated field in the text box referred to the field "JSEP_Program" that the field was part of the picture, so to speak. The spelling was correct, so what the heck

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ() function on report (XP)

    <hr>So I've built an NZ() function into the query. The field reads: Rev: Nz([JSEP_Program],"No JSEP program"). <hr>
    I saw the Rev in you definition of the field.
    What have you as field in the header : JSEP_Program or Rev (from the query) ?
    If it is JSEP_Program then Marks code should work.
    If you have Rev then my code should do it
    Francois

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: NZ() function on report (XP)

    Thanks again, Fran

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •