DCount on Left Join

K

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
 
K

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])
 
K

KPR

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

Ken Snell (MVP) said:
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>


KPR said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top