DCount on Left Join

[KPR]

Hello All,

I have two tables (tbl_1 and tbl_2) linked with a left join and I want to
count the related records between the two tables. If no records exist in
tbl_2 then I need to have the count represented as 0. I'm using the following
DCount statement and if no records exist in tbl_2 I get a #error returned.
How can I convert this error to a 0?

RECORDCOUNT: Nz(DCount("[ID]","tbl_2","[LinkedField]= " &
[tbl_1]![LinkedField]),0)

Thanks,
Ken

 

[Ken Snell \(MVP\)]

Are you doing this in a query? Assuming the answer is yes, use a subquery (I
assume that this is being used in a main query is pulling records from tbl_1
table):

RECORDCOUNT: (SELECT Count(*) FROM tbl_2 WHERE
tbl_2.[LinkedField]=[tbl_1].[LinkedField])

 

[KPR]

It was a query and the sub query worked perfectly.
Thanks for the help Ken.

Are you doing this in a query? Assuming the answer is yes, use a subquery (I
assume that this is being used in a main query is pulling records from tbl_1
table):

RECORDCOUNT: (SELECT Count(*) FROM tbl_2 WHERE
tbl_2.[LinkedField]=[tbl_1].[LinkedField])


--

Ken Snell
<MS ACCESS MVP>

Hello All,

I have two tables (tbl_1 and tbl_2) linked with a left join and I want to
count the related records between the two tables. If no records exist in
tbl_2 then I need to have the count represented as 0. I'm using the
following
DCount statement and if no records exist in tbl_2 I get a #error returned.
How can I convert this error to a 0?

RECORDCOUNT: Nz(DCount("[ID]","tbl_2","[LinkedField]= " &
[tbl_1]![LinkedField]),0)

Thanks,
Ken