Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unhappy Unanswered: Supressing Blank Fields

    I'm trying to report on fields within my database relating to satisfaction surveys. The two questions I wish to report are "what was the most relevant" and "what was the least relevant".

    The problem I have is that I wish to supress blank entries and have the next record shown. I guess its easier if I show an example:

    At the moment, it would report the following:

    __Most__________Least_______________
    All relevant
    The first part The last part
    Nothing relevant
    Everything
    None




    What I want, is for Access to report this:

    __Most__________Least_______________
    All relevant The last part
    The first part Nothing Relevant
    None Everything




    Does anybody understand what I'm saying and have any possible solutions?

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    I could probably help you but I don't currently understand what you're saying...

    Normally to suppress a blank field you would add a filter in your query to remove blank entries.

    Can you rephrase your question another way?

    Not sure what you're trying to convey here.. What would typically go in the blanks and what is the relation to the filter criteria.

    __Most__________Least_______________

    I'm assuming the below is the filter criteria.

    All relevant
    The first part The last part
    Nothing relevant
    Everything
    None

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Sorry, I guessed no one would understand.

    I have attached a quick database to help me explain this.
    The reason for this topic is to save paper. My complete report consists of 93 pages at the moment but a large number of them are blank entries

    The report currently shows all values, regardless of if they are blank.
    What I dont want is these blank fields.

    In place of the blank field, I would like the value from the next record to move up.

    the explanation I had before is useless so the excel file in the attached will hopefull explain better

    Christy
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Ditto what access_dude said

    If you only want to display records where there is an entry for both Most and Least then in your query have Is Not Null for both fields and on the same criteria line.

    If you want records displayed where either Most or Least have an entry then put Is Not Null on the first criteria line for Most and the second criteria line for Least as that results in an Or situation

    Mike

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Still not quite what I want but thanks for trying to help anyway.

    I can't explain it any better than if you look at that excel file in my zip.

    Rather than supressing blank fields, I would like it to skip to the next record.

    The report is laid in two coloums out as such:

    TITLE:MOST TITLE:LEAST

    When the program reports it displays the following

    TITLE:MOST TITLE:LEAST
    rec1: Value rec1:
    rec2: rec2: Value
    rec3: rec3:
    rec4: Value rec4: Value

    what I want it to do is:

    TITLE:MOST TITLE:LEAST
    rec1: Value rec2: Value
    rec4: Value rec4: Value

    You can see that record 2 in the least colum has replaced the previous blank.

    This is so annoying, I know excatly what I want to happen but cant explain it.

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I can't open those attachments.

    But it appears that you want to move data from record to another record.

    I will try and explain how I see your problem.

    In my data base I have a field for Business Phone and Home Phone. In some bases there are entries for both, in some cases entries for only one and of course those cases with no entry for either.

    Bus..........Home
    123..........
    ...........323
    345..........
    567..........

    To have what you want it would like this

    Bus..........Home
    123..........323
    345..........567

    In other words the entries for the phone numbers would no longer relate to a particular person.

    Perhaps another way of looking at it might be

    Field1.....Field2
    a
    b
    c
    d
    e
    ............1
    ............2
    ............3
    ............4
    ............5

    Which becomes

    Field1.....Field2
    a...........1
    b...........2
    c...........3
    d...........4
    e...........5

    Is that the situation?

    Mike

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The question here is what sort of table structure are you querying? If all these "records" are coming from different fields in a table, then you may want to look at some crosstab queries. If all the fields are coming from the same record, the NULL functions are what you should be looking at.

    What does your underlying query look like?
    oh yeah... documentation... I have heard of that.

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

  8. #8
    Join Date
    Mar 2002
    Posts
    192
    This is similar to a problem I've run into before. For me it was using sales figures from a previous record to generate a running total of current inventory levels for that week... Anyhow to answer you question and to the best of my knowledge...

    You CANNOT solve this problem using queries alone.

    The way I've been getting around this is to use code. If I have some time I'll try to sqeeze in a sample. Unless Mike or someone else beats me to it first.

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    From her examples it would appear that she does not have any relationship between the answers for Most and the answers for Least. In other words she wants a list with all the Most answers next to a list with all the Least answers.

    If that is the case then I would make a table that contained only the Most answers and another table that contained only the Least answers and each table would have a second field with no entries.

    I would then go to the table with the least entries, let's say it is the one with the Least entries and copy the column and then go to the table with the Most entries and paste into the second field.

    Mike

  10. #10
    Join Date
    Mar 2002
    Posts
    192
    Here is the solution that you are looking for. See attached zip file.
    Attached Files Attached Files

  11. #11
    Join Date
    Mar 2002
    Posts
    192
    Almost forgot.. there is one condition that it will not handle and that is if the last value (left or right) is not paired up, then it is not added.. I.E.

    Hi
    There
    This Is
    A
    Test
    OK?

    Results set

    Hi There
    This Is
    Test A

    "OK?" will not be written. To resolve this issue some further mods will be required to add the single field.

Posting Permissions

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