Handling #Deleted in outer join result RRS feed

  • Question

  • Access 2010, linked tables to Oracle back end.

    I'm running a left join query and wanting to pull all records from the left table. When the right table has no match, the column shows as #Deleted. I want to transform this to "All". I can't trap for #Deleted with iserror, isnull, or length(0) or anything else I've found. How can I get around this?

    I'm running a query like this:

    Table:  SecurityRole
    SecuredObject / Role / Value   (<== column names)
    Ledger / AllLedgers / All
    Ledger / Budget Ledgers / 2122
    Ledger / Budget Ledgers / 2123
    Ledger / Actual Ledgers / 2124
    Ledger / Actual Ledgers / 2125

    Table: ObjectPermissions
    SecuredObject / Value / ItemName  (<== column names)
    Ledger /  2122 / Division X Budget Ledger
    Ledger /  2123 / Division Y Budget Ledger
    Ledger /  2124 / Division X Actual Ledger
    Ledger /  2125 / Division Y Actual Ledger

    Select SecurityRole.SecuredObject, SecurityRole.Role, ObjectPermissions.ItemName
    from SecurityRole left join ObjectPermissions
    on SecurityRole.SecuredObject = ObjectPermissions.SecuredObject
    and SecurityRole.Value = ObjectPermissions.Value

    I want the null ItemName to show up as "All" instead of "#Deleted". If it were straight Tsql, isnull() or PLsql nvl() would do it. I haven't tinkered with a pass-through query to Oracle and don't know if I'll have issues there. Is there any solution other than a pass-through query?

    All help is appreciated.
    Ernest Ostrander

    Friday, August 14, 2015 9:21 PM


  • As far as I know the only way you can have a #Deleted message is when you delete something and the object displaying the record is not refreshed. You shouldn't be seeing #Deleted in any other way. If you are then you may be deleting something without refreshing the record.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Saturday, August 15, 2015 3:56 AM