Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query and I have a table that I am joining to this query by rep number. In the table is a list of rep numbers and a field called RVP that I want to bring into my query. Sometimes on the query side, the Rep field is blank. For those (in the table of Reps with the RVP field), I put a blank and and RVP of unknown. When I join this table to my query, I do an outer join. I am not picking up the fields where the rep number field is blank.

    How would I do this. I want those records that have a blank Rep number to get an RVP of unknown.

    I put a blank record in the rep table with an RVP of unknown but I am not getting anything for the RVP when I join it to the query by Rep number. Is there anyway to do this?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You should be able to put an expression based on the Rep Number in the field that looks something like:
    Code:
    DisplayRVP: IIF(IsNull([Rep Number],"Unknown",[RVP])
    This should give you an "Unknown" value for any of the right joins where there isn't a matching Rep number in the table.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply. I figured I couldn't do anything in a table and had to create a field in the query to accomodate this. The table had a blank for rep number with unknown in the RVP field. I should have known better. My manager said it could be done - duh!

Posting Permissions

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