Results 1 to 8 of 8
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    If a field is null still produce results?

    ProfileBusiness1 - Copy.zip

    Hi all,
    I have a query "qryBusinessContact_Rpt" and is going to be for a report. DB Attached....

    The problem i am having is that there are three results (Phone, Email, and Title) and if one or more of those are Null, then it does not produce the record.
    Basically it takes the phone Number and IsPrimary=True, same for email and title...

    Some times I dont have all three items (Phone, email, or title) but if I have any of them i want the record in my query. I have tried many left, right joins


    with no success. I have tried several IIfs and no luck there either... I would love some assistance with this.

    The goal is to run the query for a report, and if i only have one or two of the conditions it still produces the record and leaves the missing items blank.
    Example, If I have a phone number for one contact, but not an email or title, I still get a record in my query but leaves the two i dont have blank.
    214-542-9664 Blank Blank If this makes sense.

    Thanks a lot all, ps... I had to take out 5 tables to make able to upload this and they are all about addresses so should not affect this....
    Dave

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wow. Have you ever complicated that table schema and it doesn't look like you've done yourself any favours with it. You've split contact phone/email/etc. into separate tables, joined tables with mis-matched names (BusinessHelperID to BusinessTitleTypeID), have what looks like repeating tables (BusinessContacEmail stuff). There's no way I (for one) will make any sense of it all without (probably) hours of study and back and forth dialog. I looked, but have to bow out. Sorry.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Maybe something like in this updated file?

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Vlad,
    That is perfect! Thanks for the work, thanks for giving me some knowledge...
    That did the trick....
    Thanks again!
    Youre awsome
    Dave

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Micron,
    No problem! Thanks for taking a look.
    Dave

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You do know that "Memo" and "Domain" are reserved words in Access??




    I think I counted 8 tables with a field name of "IsActive" and 5 tables with a fields named "IsPrimary", "Created" and "Modified".
    I never have duplicate field names - I use a 3 letter prefix made up of the initials of the table.

    For example, I would have "tblBusinessNameDef" for the Business table and the fields would be:
    "bnd_IsActive"
    "bnd_IsPrimary"
    "bnd_Created"
    "bnd_Modified"


    For the table "tblBusinessContactTitle", the fields would be named:
    "bct_IsActive"
    "bct_IsPrimary"
    "bct_Created"
    "bct_Modified"



    For the table "tblBusinessPhoneContact", the fields would be named:
    "bpc_IsActive"
    "bpc_IsPrimary"
    "bpc_Created"
    "bpc_Modified"


    It might take a little getting used to, but once you get used to it, it is easy to tell which table a field is in.

    Well, it works for me......

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome Dave!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi ssanfu,
    Thanks, that makes a lot of sense. I will remember this! Nice touch
    Dave

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Added field for new option group results in null error
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 11-10-2020, 03:35 PM
  2. Replies: 1
    Last Post: 01-29-2018, 01:15 PM
  3. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  4. Replies: 3
    Last Post: 09-12-2013, 02:18 PM
  5. Replies: 17
    Last Post: 09-24-2012, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums