Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a report a table (2000)

    I know I've seen this before but I can't find it now so I'll ask.

    I'm making a report from a table that has information about individuals in the table. For some of the individuals, all of the fields for the report have information in them, for others, just a few of the fields have information in them and for some of the individuals, all of the fields to be included in the report are empty. For the report, I want to eliminate all the individuals that are in the table that have a null value in all of the report fields, but I can't seem to get it to work right. I am trying to use a query with all the fields I want in the report, but how do I set it up so that all of the individuals that have a null value in all the report fields, not just some of them are eliminated, but all those that have at least one of the fields filled in are included?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making a report a table (2000)

    Take a look at the thread starting in <post#=261336>post 261336</post#> where a similar question about excluding records with nulls was addressed. In that thread, the idea was to test for nulls in any of the specified fields. In your case, it would be somewhat different because you want to exclude only records where all the fields you're examining are null. What you could do is create a calculated expression in your query that would look something like this:

    Expr1: Len([Field1] & [Field2] & [Field3] & "") > 0

    This assumes that the fields you want to test are called Field1, Field2 and Field3. You would need to substitute the actual field names for however many fields you need to test. Then in the criteria line in your query, you would add a True under this expression. What you're telling Access is that if you concatenate the values in all those fields and then concatenate a zero-length string to it, the length of the result has to be greater than zero or you don't want to see the record. The zero-length string is necessary in case the fields are all numm, since you can't test the length of a null value.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a report a table (2000)

    Thank you very much, that worked.

    Let me also ask a related question. In the report layout as it is now, I have each of the included fields side by side in a line. If a particular individual has something in the first field it shows up just right of their name. The second field shows up just right of the first field and so on. If the individual doesn't have information in the first or second field, just the third for instance, there is a gap between the name and the third field in the report where the first and second fields would be displayed if they had a value. Is there a way that if the first and/or second fields are blank the third field would show up just right of the name? In other words, it would place only fields with information in them in the report for that particular individual.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making a report a table (2000)

    You could do it with a lot of programming but it wouldn't be the best report design. Usually, you have column headings in the page header or group header of the report to indicate what a particular value represents. Even without that, it's best to leave the data in a fixed order because it is confusing if a value is in one place in one record and somewhere else in another.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a report a table (2000)

    Ok, Thank you for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making a report a table (2000)

    As Charlotte remarks, in a report with the fields side by side (a "tabular" report), the user usually expects fields to be lined up vertically. But if you really want it the way you describe, you should replace all the text boxes to the right of the name by one wide text box, with a control source of the form

    =[Field1] & (" "+[Field2) & (" "+[Field3])

    Repeat as many times as needed, and substitute the correct field names. This will separate the items by a space, if you want something else, replace " " by ", " for example. This will work OK for text fields; if you have other types, it's a bit more complicated:

    =[Field1] & (" "+CStr(Nz([Field2],""))) & (" "+CStr(Nz([Field3],"")))

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a report a table (2000)

    Thanks Hans, that helps.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a report a table (2000)

    Hi Charlotte, The Expression code you suggested works great, but it does something that I cannot seen to eliminate. Including the expression field, there is 15 fields in the query. When the query is run, a dialog box requesting a parameter value appears for one of the fields. If the box is left empty, the query runs correctly as I want it to, but if anything is enter in the box, (whether text or number) the query reports on all records from the table. It appears putting a value in causes the expression field not to run at all and that is why all the table records appear. Any idea why?

  9. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a report a table (2000)

    Thanks Charlotte, that did it. In the code I had made one field plural but the field in the table was not plural.

    You know, all the help that you, Hans and other provide here is such a blessing. I try and try all day to get something to work and when I post the problem here; I have an answer in as quick as a few minutes sometimes. I have posted many questions here and I feel at times that posting so many is abusing your generosity. You provide a very beneficial service here. If you ever feel my username is seen here too often, just let me know. Thanks again

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making a report a table (2000)

    You shouldn't be getting a parameter box unless you misspelled the name of one of the fields. Check your expression carefully. That also explains why you get all the records. When you enter something in response to the parameter dialog, your expression will return that entered value in all records, whether or not the fields are null.
    Charlotte

Posting Permissions

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