Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    34

    Unanswered: ignore blank field in reports

    I ran a query that has some fields that are empty. Now I want to create a report from that query. I want it to ignore the empty fields and connected label box, but I want to capture the rest of the data from the record. If I use the criteria in the query it does not give me the whole record.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Edit your query to discard null fields

    SELECT *
    FROM yourTable
    WHERE nullField IS NOT NULL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What do you mean by ignore the null fields and labels but keep the record?

  4. #4
    Join Date
    Apr 2004
    Posts
    34
    So .. go to the design view of my query and under criteria enter

    SELECT *
    FROM Circuit1
    WHERE Circuit1 IS NOT NULL

    Do this for each of my 20 fields…

    SELECT *
    FROM Circuit2
    WHERE Circuit2 IS NOT NULL

    Ect………

    (1 record has 20 fields that can be populated.)

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh.. Maybe I don't understand what you're doing here.

    what are these 20 fields,

    What do you mean by "one record"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2004
    Posts
    34
    I have a database set up that is for testing circuits.
    so line 1 or record 1 consists of the results of 1 test. These circuits may be on (blank) or off (Off).
    SO... record 1 looks like this:

    Circuit1 Circuit2 Circuit3 Circuit4
    Record 1 MISSING OFF
    Record 2 OFF OFF OFF
    Record 2 OFF MISSING


    My Query mirrors the table.
    I want a report that will only tell me the fields that are populated. I don’t want to have any of the labels or the blank spaces on my report.

    Ie….

    Record 1
    Circuit1 MISSING Circuit 3 OFF

    Record 2
    Circuit1 OFF Circuit2 OFF Circuit 4 OFF

    You see how just unchecking the show box on the query will not work for me.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Lets try this a different way.


    Post your exact table structure, and query SQL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    tkacoo,

    Did you see my reply to your other similar post about..."unchk box won't do" ???? Find that post and try the suggestions I input for you. Just might work.
    Bud

  9. #9
    Join Date
    Apr 2004
    Posts
    34
    yes I saw your posting. I already have it configured for the box to come up blank. What I want is for the report to skip or hide the label box also if the answer is null.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think in the OnFormat event you could get away with:

    label1.visible = checkbox1.value
    label2.visible = checkbox2.value
    .....

    Easier than 30 if statements if it works.
    Last edited by pootle flump; 05-06-04 at 12:09. Reason: Tab error

Posting Permissions

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