Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unanswered: Displaying fields with value only

    How can I hide empty fields in a report? The table "Batch" which I'm using has the fields "City", "State" and "Province". Now when I pull a report I want the fields which have a value entered in them only to be displayed. That is; if for a particular record; the field "City" and "State" have values in them and the field "Province" is a blank then only "City"
    and "State" should be displayed and the field name "Province" with a blank value should not appear and the next record should immediately follow without leaving any space. In a nutshell only a field with value should appear on a report otherwise the field with no value should be hidden. Can anyone help?
    sahil

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    You could try the following :

    -remove all the labels on your report
    -create a query to use as recordsource instead of your table
    -in that query, concatenate the labeltext with the entry of the field if there is one.

    I explain :
    normally, in the grid for your query, you would put [City] if you want to have the name of the city in the query result.
    When you replace this with the following :
    CityText : IIF(IsNull([City]);"";"City : " & [City])
    the result will be empty if the field [City] is empty. If the field [City] is not empty (suppose NewYork is entered), the result will be :
    'City : NewYork'

    When you now use CityText as source for the textfields on your report, you do not need to use labels and therefore they will not appear on empty fields.

    I know it looks complicated on first sight but try it!

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Wink

    Another way to do this is by using wildcards

    Under the queries criteria type:

    Like "*"

    This should return only true values ommiting any blank spaces.

  4. #4
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Originally posted by christyxo
    Another way to do this is by using wildcards

    Under the queries criteria type:

    Like "*"

    This should return only true values ommiting any blank spaces.
    Sorry dude, no will do!
    Our friend raoof698 wants to display half the record on his report. Your solution will still display the labels on the report, even if the field is empty.

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Yep you're right. Sorry about that.

    Thats a nice little touch there,

    I like your style man!

  6. #6
    Join Date
    Jan 2004
    Posts
    16
    Herman,
    That sounds good! I tried putting the condition you gave in the query, but it gives me a syntax error; Also can you help me with the query since I'm preparing the database and reports in MS ACCESS. Thanx in advance
    sahil

  7. #7
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    The code I gave is not the condition in the query, it's the query itself.
    I'd like to send you the example but as i'm at my work, our net policy does not allow sending or receiving files by internet (yes, i know, it sucks...)

    But here is the SQL-script for an example :

    SELECT IIf(IsNull([City]),"","City : " & [City]) AS CityText, IIf(IsNull([State]),"","State : " & [State]) AS StateText, IIf(IsNull([Province]),"","Province : " & [Province]) AS ProvinceText
    FROM tblAddresses;

    Simply copy the script above and put it in a query. It assumes a table tblAddresses with the fields City, State and Province.

    You might get a syntax error on the delimiter. Depending on your regional settings, you might need to use a "," instead of a ";" in the script

    Once you have the query, simply build a report on it but don't use any labels for the fields.

    Good luck

  8. #8
    Join Date
    Jan 2004
    Posts
    16
    thanks for the help guys.
    sahil

  9. #9
    Join Date
    Jun 2011
    Posts
    2

    Hiding fields with no value

    Totally new to Access, as in 2 months, I am having roughly the same issue, if I understand it correctly.
    When I run a report from a query in Access 2007 it brings back empty cells which leave empty spaces in the report. I would like to hide the empty spaces and have everything move up/left or right (basically adjust for the hidden cell). If I enter ‘isnull’ in the criteria it excludes the whole line. Everything in the line, I just want the empty cell excluded/hidden

    The query reports on: Name, University, and Graduation date

    The report looks like:
    Name: John Doe
    University: Kansas State University
    Graduation date: 8/1/2006
    Name: Jane Doe
    University: Kansas University
    Graduation date: 8/1/2007


    If there is no data in a cell it looks like this:
    Name: John Doe

    Graduation date: 8/1/2006
    Name: Jane Doe
    University: Kansas University
    Graduation date: 8/1/2007


    I would like for it to exclude or hid the empty cell, looking like this:
    Name: John Doe
    Graduation date: 8/1/2006
    Name: Jane Doe
    University: Kansas University
    Graduation date: 8/1/2007
    Last edited by reignmaniac; 06-22-11 at 18:09.

  10. #10
    Join Date
    Jun 2011
    Posts
    2
    I forgot

    Herman
    Not sure what you mean by put the script in a query, totally new to Access.
    SELECT IIf(IsNull([City]),"","City : " & [City]) AS CityText, IIf(IsNull([State]),"","State : " & [State]) AS StateText, IIf(IsNull([Province]),"","Province : " & [Province]) AS ProvinceText
    FROM tblAddresses;f

Posting Permissions

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