#deleted on linked table select query

[Mark]

I'm doing a select query on linked tables where the source data is in an
AS400 database. It's a left outer join query (tableA left outer join tableB
on tableA.ID = tableB.ID)

The problem happens when tableB.ID doesn't exist. I want to return a field
from tableB (call it MemberNum), but MemberNum shows as #Deleted for those
records where tableB.ID doesn't exist. I tried Nz and iif with isnull and
neither work. Any ideas on how to change that #Deleted to a blank or 0?

Thanks!
-Mark

 

[Golfinray]

Try IIF(tableb.id]is null, "0",[tableb.id])

 

[Mark]

Hi,

Thanks for your response. Yeah, I tried that too. Didn't work. I actually
found what the problem was. For anyone curious, it's at
http://support.microsoft.com/kb/128809

Basically, as I read it, it's the way Access queries the db. The suggestion
I ended up using was to convert to a passthrough query, then using the AS400
coalesce function to change the nulls to 0's.

Try IIF(tableb.id]is null, "0",[tableb.id])

I'm doing a select query on linked tables where the source data is in an
AS400 database. It's a left outer join query (tableA left outer join tableB
on tableA.ID = tableB.ID)

The problem happens when tableB.ID doesn't exist. I want to return a field
from tableB (call it MemberNum), but MemberNum shows as #Deleted for those
records where tableB.ID doesn't exist. I tried Nz and iif with isnull and
neither work. Any ideas on how to change that #Deleted to a blank or 0?

Thanks!
-Mark