Outer join to a blank

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote