Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    22

    Question Unanswered: Help! Access Query that omits field if NULL!

    I need to set up a query that omits fields if NULL or empty..and thus far havent been able to do so or figure it out....youre help is greatly appreciated

    thanks,

    John

  2. #2
    Join Date
    May 2003
    Posts
    4
    Have you tried IsNull ?

    In your query criteria you could put something like:

    If IsNull(FieldName) = False

    This would only show fields which were Not Null.

    Sfez

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Is Not Null

    In query design view, type:

    Is Not Null

    In the criteria section under the field you want to check.

    This will find only records which have data in that field.

    What data type is the field?

  4. #4
    Join Date
    May 2003
    Posts
    22
    the data is a hyperlink to a picture on my server

  5. #5
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    The answer to you question (in both posts) is NO

    If a field is empty then the "Caption"(heading) will still appear in a query.

    In a "single form" you can hide the control (visible=false) if it is "empty"

    I think you could create a query with code that only selected fields that were "empty" but why you would want to I have no idea.

    See my attached database, look at the queries and then open the form
    cycle through the records and you will see that the FAX do not appear in all of them.


    Hope this was of some help



    John A

    PS maybe you could explain what you are trying to do?
    Attached Files Attached Files

  6. #6
    Join Date
    May 2003
    Posts
    22
    I would love to see your example but it is telling me that it is read only and when I check the properties it doesnt seem to be??? I am running access xp

  7. #7
    Join Date
    May 2003
    Location
    Providence, RI
    Posts
    12

    Angry

    If I understand what it is you are trying to do correctly, I think that what you are looking for is an SQL statement that excludes empty fields, correct?

    In that case, you would use:

    SELECT tbl.*
    FROM tbl
    WHERE [field_name] IS NOT NULL;

    Give that a try and let me know if it works.

  8. #8
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    The sample was written in access 97, when you try and open it, you will be asked to either open it or convert it . The answer is up to you. If you just want to view it then just open it ( you will get a warning that it is read only) or you can convert it to xp.

    The choice makes not difference, you would still be able to view. I do not have access xp, but i do have access 2000 and I both opened and converted it with this program.


    John A


    I have attached another copy saved in access 2000
    Attached Files Attached Files
    Regards,



    John A

Posting Permissions

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