Solved

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

Posted on 2009-04-04
5
762 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:zoomtrac
5 Comments
 
LVL 2

Expert Comment

by:pskeens
ID: 24070287
How about

Iif(Count([childTable].[field]) > 0, "YES", "NO")
0
 
LVL 2

Accepted Solution

by:
pskeens earned 500 total points
ID: 24070290
You can do DCOUNT this way as well

HAS_CHILD: IIf(DCount("*","ChildTable",[MasterField]=[ChildField])>0,"YES","NO")
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24070527
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
 
LVL 30

Expert Comment

by:hnasr
ID: 24071419
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
 

Author Closing Comment

by:zoomtrac
ID: 31566689
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Record with #Error 8 33
TSQL Challenge... 7 35
Microsoft Access to CSV file from VBA 3 25
2 subforms 1 main form 1 13
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question