Results 1 to 14 of 14

Thread: Hide null values in MS Access report

  1. #1
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900

    Hide null values in MS Access report

    I have an MS Access report that lists a number of fields. The way it is setup now if a field on the report has a null value in the table then it shows the title and then an empty space in the report. How do I get the report to only show the fields that have values and not the fields that do not have values?
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  2. #2
    Administrator Philip's Avatar
    Join Date
    May 1999
    Location
    Jacksonville, Florida, United States
    Posts
    10,328
    Blog Entries
    6
    I'm not a MS Access expert, but you'd probably need to change your query to exclude empty records. The SQL should look something like:

    SELECT ....
    FROM ....
    WHERE fieldname IS NOT NULL

    or:

    SELECT ....
    FROM ....
    WHERE fieldname != ""

  3. #3
    SG Enthusiast Far-N-Wide's Avatar
    Join Date
    Jan 2000
    Location
    His Tavern of Solitude
    Posts
    1,160
    It's been too long since I've done anything with Access. I did have a very similar problem with null fields though. I wish I knew the correct statement your looking for. But years ago, I had an MS Access open log or report form. That took multiple inputs from different users, a report was needed every 24 hours.

    I do Think that if the data was null on some fields. I had it Sub in a comment like N/A or data is not valid. Whatever fits your project or report. Or I think you just need a check for a value greater then zero. If so move that data field to a new table. Display the data on the new table to your report form.

    I can't think how this was exactly done... Sorry I can't say how I did it... but at least you have two avenues to follow up on.

    Cheers man
    Good luck
    USAF RETIRED

  4. #4
    SG Enthusiast Far-N-Wide's Avatar
    Join Date
    Jan 2000
    Location
    His Tavern of Solitude
    Posts
    1,160
    Oh.. sweet... Phill to the rescue. I took to long poking the keys.
    USAF RETIRED

  5. #5
    Administrator Philip's Avatar
    Join Date
    May 1999
    Location
    Jacksonville, Florida, United States
    Posts
    10,328
    Blog Entries
    6
    You may also be able to do simething with VBA:

    If Not IsNull (.....) Then
    .....
    End If

  6. #6
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    I think the VBA will be necassary since I am trying to get the tag names in the report to not show if the value is null (currently the data is pulled and I tried the SQL line--IS NOT NULL, but the tags still show). I will try the VB script and see what is produced. Thanks for the help all!
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  7. #7
    Administrator Philip's Avatar
    Join Date
    May 1999
    Location
    Jacksonville, Florida, United States
    Posts
    10,328
    Blog Entries
    6
    Also keep in mind that "NULL" is an unknown value, it is treated differently than an "empty" field, that's why I gave two different SQL queries. I'm not sure what the case is in your DB.

  8. #8
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    Negative, did not work.

    What I am after is in the report if the data field is blank (Criteria) then I do not want the label to appera (Criteria Label). The query that is in place does what I need it to do, I think there is something I am missing in the Report properties?
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  9. #9
    Administrator Philip's Avatar
    Join Date
    May 1999
    Location
    Jacksonville, Florida, United States
    Posts
    10,328
    Blog Entries
    6
    Oh, ok, I didn't realise you were trying to hide the labels.

    You might want to try those:
    http://www.thescripts.com/forum/thread590878.html

  10. #10
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    I am giving it a whirl...dumb question, with the following line of code--
    If Len(Nz(findings.[Related Prior Year Finding(s)])) > 0 Then

    What is the Len and Nz suppose to be (or what does it relate to)? Can you tell I am not too VB savy
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  11. #11
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    I get an Object Required error and the debugger points me to this line of code:
    If Len(Nz(findings.[Related Prior Year Finding(s)])) > 0 Then
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  12. #12
    Administrator Philip's Avatar
    Join Date
    May 1999
    Location
    Jacksonville, Florida, United States
    Posts
    10,328
    Blog Entries
    6
    I imagine "Related Prior Year Finding(s)" should be some type of label name, pointer to the label, rather than the text itself.

  13. #13
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    this is the code that I have now.....

    If Len(Nz([Related Prior Year Finding(s)]---this is the field name in the table---)) > 0 Then
    [Related Prior Year Finding(s)_Label]--this is the label name---.Visible = True
    Else
    [Related Prior Year Finding(s)_Label]--this is the label name---.Visible = False
    End If
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

  14. #14
    Advanced Member
    Join Date
    Apr 2001
    Location
    Delaware
    Posts
    900
    Update...got it working...now is there a way to get it to shrink the space? Currently the lable/field is only shown on the report if there is data, if not data then it does not show up; however it leaves a blank space as if the field were there....
    Fee was a buddhist prodigy...
    Long past the age of maturity...
    Someday he knew it would set him free...
    Like it did for Floyd...
    The Chimpanzee...

Similar Threads

  1. WAP54G Web Configuration access woes
    By MadMattUK in forum Wireless Networks & Routers
    Replies: 6
    Last Post: 06-09-08, 04:31 PM
  2. WLAN/Wi-Fi tweaking
    By anttu in forum Wireless Networks & Routers
    Replies: 10
    Last Post: 05-01-06, 02:11 PM
  3. Web access or router access not both???
    By berrybry in forum Networking Forum
    Replies: 5
    Last Post: 10-03-05, 10:01 AM
  4. Access Reports
    By aaghd in forum Software Forum
    Replies: 0
    Last Post: 03-18-05, 02:10 PM

Bookmarks

Posting Permissions

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