Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null Fields on Report (97)

    I am generating reports based on a form used for special product pricing for each customer. Each form has multiple service codes but a customer could have anywhere from 1 to 10. When I generate the report the customers that only have 1 service code looks like this:
    Customer: ABC
    Account #: 1234
    Service Code: 987
    Service Code2:
    Service Code 3:
    Service Code 4:

    You get the picture. Is there a way where I can generate this report from my query and if those fields are null for them not to show up on the report?

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

    Re: Null Fields on Report (97)

    Can you tell us a bit more about the underlying table structure? For instance, do you have a table with customer info and a related table with service codes, in which each customer - service code combination is a record, or does the customer table have ten fields ServiceCode1 to ServiceCode10 of which a varying number is filled, or something else? The answer to your question depends very much on the structure of the tables and their relationships.

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Fields on Report (97)

    Unfortunately this was my first time building a database and I have a customer table with ten different fields for the service code. When I first built the database I linked the table to a service code table but I couldn't figure out a way to input multiple service codes without having to re-enter customer data over and over again. Each customer can have multiple account numbers, different service codes, and special pricing expiration dates. But to answer your question yes all the info is basically held in the customer table. I have imported a service code spreadsheet into the database if that helps, but at this time they do not have a relationship because it only links to the field service code(foreign key) and service code(primary key) in the service code table. In the customer table it is service code1, 2, 3 etc, therefore those could not have a relationship.

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

    Re: Null Fields on Report (97)

    If you can live without labels for the service codes, you can get the effect you want by setting the CanShrink property to True for the Detail section and for each of the text boxes bound to one of the service code fields. Take care that there is no vertical overlap between the text boxes, otherwise CanShrink will not work.

    If you ever have some time on your hands, you might consider moving the service codes to a separate table linked to the customer table by customer ID. But since it will entail a major overhaul of the database, don't do it in haste.

Posting Permissions

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