Results 1 to 5 of 5
  1. #1
    sakthivels is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    10

    Hiding fields that contains empty records

    Greetings to everyone, My name is Sakthivel. I am creating a database to maintain the client details for my office. I have some issues displaying the records in report.



    My database has the following fields:
    Client_Name, Address1, Address2, City, Pincode, Landmark, Office_Tel, Home_Tel, Mobile1, Mobile2.

    Some records in the table has empty values for some fields.

    Now I want to display the records. Since some records has empty values they display as blank lines. How do i suppress them. I tried setting the field property can shrink to yes but of no avail. I also tried using a label field and set the control source as follows

    =Trim([Address1]) & "," & Chr(13) & Chr(10) & Trim([Address2]) & "," & Chr(13) & Chr(10) & Trim([City]) & " - " & Trim([Pincode]) & "." & Chr(13) & Chr(10) & "Landmark : Near " & Trim([Landmark]) & "." & Chr(13) & Chr(10) & IIf(IsNull([Tel_Home]),"Phone : ","Phone : " & [Tel_Home]) & Chr(13) & Chr(10) & IIf(IsNull([Mobile1]),"","Mobile : " & [Mobile1]) & Chr(13) & Chr(10) & IIf(IsNull([Mobile2]),"Mobile : ","Mobile : " & [Mobile2])

    Can some one please help me in this regard.

    With Kind Regards,
    Sakthivel

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you also set the Detail Section CanShrink property to YES.

  3. #3
    sakthivels is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    10

    Requesting assistance

    Yes I also tried setting the can shrink property of detail section to yes. But the problem is I also need the label along with the data. For example I want to display the address as:
    Sakthivel
    #12, Ist Main Road,
    T.Nagar,
    Chennai - 600 017
    Office:+91-44-xxxx xxxx
    Home: +91-44-xxxx xxxx
    Mobile: +91 xxxxx xxxxx

    If the labels are present the CanShrink property is of no use.

    Records which have no data for fields are displayed as empty spaces example
    Sakthivel
    #12, Ist Main Road,
    T.Nagar,
    Chennai - 600 017
    Office:
    Home:
    Mobile: +91 xxxxx xxxxx.

    Could you please suggest me some other way to fix this. I am also trying and if I come across one I'll post here.

  4. #4
    sakthivels is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    10

    Hiding fields with empty value

    Respected RuralGuy,

    Atlast I found a solution for this issue. I tried the following expression and it worked.

    =Trim([Address1]) & "," & Chr(13) & Chr(10) & Trim([Address2]) & "," & Chr(13) & Chr(10) & Trim([City]) & " - " & Trim([Pincode]) & "." & Chr(13) & Chr(10) & "Landmark : Near " & Trim([Landmark]) & "." & Chr(13) & Chr(10) & IIf(IsNull([Tel_Home]),"","Phone : " & [Tel_Home] & Chr(13) & Chr(10)) & IIf(IsNull([Mobile1]),"","Mobile : " & [Mobile1] & Chr(13) & Chr(10)) & IIf(IsNull([Mobile2]),"","Mobile : " & [Mobile2] & Chr(13) & Chr(10))

    What I did was to add the carriage return / line feed information in the true part of the iif expression and it works.

    Anyway thanks for spending time to help me.

    Regards
    Sakthivel

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Excellent! So much more can be learned when you solve problems yourself. Thanks for posting back with your success.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. multiple fields in separate records
    By Fredo0709 in forum Database Design
    Replies: 9
    Last Post: 04-09-2010, 12:23 PM
  2. How to get rows with empty fields as well?
    By Orabidoo in forum Queries
    Replies: 10
    Last Post: 04-24-2009, 06:08 PM
  3. Hiding subreports
    By aouellette in forum Reports
    Replies: 0
    Last Post: 09-12-2008, 08:02 AM
  4. Replies: 1
    Last Post: 10-26-2007, 07:29 AM
  5. hiding forms
    By Halocarbon in forum Access
    Replies: 1
    Last Post: 12-09-2005, 09:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums