Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Spokane, WA
    Posts
    81

    Unanswered: Hiding Empty Fields

    I have the following SQL in a query.. (minus my comment marks):

    SELECT Tbl_Person.[IR Number], Tbl_Person.Relationship, Tbl_Person.LastName, Tbl_Person.FirstName, Tbl_Person.Middle, Tbl_Person.Street_address, Tbl_Person.City, Tbl_Person.AddressState, Tbl_Person.Zip, Tbl_Person.telephone_number, Tbl_Person.[Cell phone], Tbl_Person.[Type of Id], Tbl_Person.[Badge Number], Tbl_Person.IDType2, Tbl_Person.Number2, Tbl_Person.[Supervisor's Name], Tbl_Person.work_tele_number, Tbl_Person.[Department Number], Tbl_Person.[Supervisor's/work telephone], Tbl_Person.[drivers license], Tbl_Person.state, -Tbl_Person.race, Tbl_Person.sex, Tbl_Person.DOB, -Tbl_Person.Height, Tbl_Person.Weight, Tbl_Person.Eyes, -Tbl_Person.Hair, Tbl_Person.Complexion, -Tbl_Person.Clothing, Tbl_Person.SMT, Tbl_Person.[Job Description and/or assigned work area], Tbl_Person.dlstate, Tbl_Person.dlstate2, Tbl_Person.roomnumber
    FROM Tbl_Person;

    *But* would like the following fields to not show on a report if they are not filled in. How can I do that?

    Tbl_Person.race, Tbl_Person.sex, Tbl_Person.DOB, Tbl_Person.Height, Tbl_Person.Weight, Tbl_Person.Eyes, Tbl_Person.Hair, Tbl_Person.Complexion, Tbl_Person.Clothing, Tbl_Person.SMT (I marked it by a "-")

    Jacque

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    In the OnFormat event for the 'Details' section of your report enter this code:
    Code:
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
           If IsNull(Ctrl.Value) Then Ctrl.Visible False Else Ctrl.Visible = True
        End If
    Next Ctrl
    Other controls you can add to this:

    acBoundObjectFrame
    acOptionButton
    acCheckBox
    acOptionGroup
    acComboBox
    acPage
    acCommandButton
    acPageBreak
    acCustomControl
    acRectangle
    acImage
    acSubform
    acLabel
    acTabCtl
    acLine
    acTextBox
    acListBox
    acToggleButton
    acObjectFrame



    Hope this helps

  3. #3
    Join Date
    Sep 2003
    Location
    Spokane, WA
    Posts
    81

    Doing Something Wrong

    I must be doing something wrong, it's not working.

    I have attached a screen shot of my visual basic.
    Attached Thumbnails Attached Thumbnails image1.jpg  

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Sorry there was a error in the code (i've been doing that a lot lately )
    Code:
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
           If IsNull(Ctrl.Value) Then Ctrl.Visible = False Else Ctrl.Visible = True
        End If
    Next Ctrl
    Please note though....this simply makes the report field invisible.

  5. #5
    Join Date
    Sep 2003
    Location
    Spokane, WA
    Posts
    81

    Beautiful!

    Perfect, a thing of beauty.

    I love ya!

    Thank you so much.

    Jacque
    jack@runway.net

Posting Permissions

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