Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    avoiding nulls in query (a2003)

    I have a tblConsumer with lngConsumerID as primary key and tlkpPhone with lngConsumerID as foreigh key. The main form frmConsumer has fsubPhone. What's the best way to handle it when there is no phone number but it can't be left null because it affects query results?

    I hope to apply the same strategy with an email subform and a home address subform, all linked on ConsumerID.

    Eli

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

    Re: avoiding nulls in query (a2003)

    It's not clear to me how a phone number being null would affect query results. The main form should have tblConsumer (or a query based on tblConsumer) as record source. The lookup tables should not be part of the record source of the main form. The subform should have the lookup table (or a query based on the lookup table) as record source.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: avoiding nulls in query (a2003)

    I'm sorry, I wasn't very clear. The query is a multitable query designed to identify specific products bought during a date range. I want to followup with the buyers via mail, email or fax and wanted to use the same query. However, as soon as I include tlkpPhone, tlkpEmail, tlkpAddrHm in the query any Consumer without any one of those gets filtered out.

    I wondered if there was a good way to prevent users from leaving these subforms blank or perhaps creating a dummy entry if they do leave it blank so that my single query works.

    If this approach is flawed, could you point me in a better direction.

    E

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

    Re: avoiding nulls in query (a2003)

    You can make the joins in the query into outer joins:
    - Double click a line joining tblConsumer and one of the lookup tables.
    - Select the option to include ALL records from tblConsumer and only those records from the lookup table where the joined fields are equal.
    - Click OK. The line should now display an arrow pointing to the lookup table.
    - Repeat for the other lines joining tblConsumer to a lookup table.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: avoiding nulls in query (a2003)

    I can't believe I've never had use for a outer join before. It certainly is going to come in hand now. Thanks for redirecting me.

    E

Posting Permissions

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