IIf() in Query to return Yes/No if sub-records exist or not MS-Acces

I need to know how to get a field in a query to indicate by "yes" or "No" whether records exist in a child table of one of the tables in the Query. As yet the child table is not represented in the query.
I have tried wrapping dCount() in IIf() but no luck:
iif(dCount("ChildTable.Key","ChildTable","ChildTableKey = ParentTable.key") > 0,"Yes","No")
The query populates a listbox
zoomtracAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pskeensConnect With a Mentor Commented:
You can do DCOUNT this way as well

HAS_CHILD: IIf(DCount("*","ChildTable",[MasterField]=[ChildField])>0,"YES","NO")
0
 
pskeensCommented:
How about

Iif(Count([childTable].[field]) > 0, "YES", "NO")
0
 
milduraitCommented:
You can do this using EXISTS keyward

Column Definition:
HasChldren: EXISTS (SELECT * FROM CHILDTABLE WHERE PARENTTABLE.ID=CHILDTABLE.ID)

Full SQL Syntax:
SELECT
   *,
   EXISTS (SELECT * FROM CHILDTABLE WHERE PARENTTABLE.ID=CHILDTABLE.ID) as HasChildren
FROM PARENTTABLE
0
 
hnasrCommented:
Try this:
tparent:
id
1
2
3
4
tchild:
id      idparent
1      1
2      1
3      2

SELECT tparent.id, IIf(IsNull([idparent]),"No","Yes") AS Expr1
FROM tparent LEFT JOIN tchild ON tparent.id = tchild.idparent;

id      Expr1
1      Yes
1      Yes
2      Yes
3      No
4      No
0
 
zoomtracAuthor Commented:
Well it was pretty close just a syntax issue:
HasRecords: IIf(DCount("*","tblMLMtrans","[tblMLMtrans].[MLMID] =" & [MLMID])>0,"Yes","No")
where tblMLMtrans is the child table NOT in the Query and the MLMID refers to parent table field in query (needed the ="& syntax) is this because it refers to a value in the query itself?
0
All Courses

From novice to tech pro — start learning today.