IIF function syntax

[kris335]

I have 2 tables, one called "Parent" and one called "Child". I also have a Family-App ID that connects the two.

What I am trying to do is create an unbound text box on the "Parent" form to return the children from the "Child" table that have the same Family-App ID as the parent. If there is no match, I want it to return nothing.



My current expression, which is NOT working, looks like this:

=IIf ([Child]![Family-App ID] = [Parent]![Family-App ID], “[Child]![Child Name]”,”” )
So I have 3 questions:
1. Can you even return results from an IIF statement into an unbound text box or should I create a field within the "Parent" table/form for the info?
2. How doI tell it to return nothing if the statement is not met?
3. What's wrong with the expression? I'm getting an operator-missing-an-operand error when I try to use it.
Thanks for any and all help. If you need more info on what I'm trying to do, please let me know!

[alansidman]

Try changing to this:
=IIf (Forms![Child].[Family-App ID] = Forms![Parent].[Family-App ID], “Forms![Child].[Child Name]”,”” )
but what I would do is make the child form a subform of the parent form and insert it into the parent form. In this manner, they would be linked and the children information would be available. Look at this video.

http://www.datapigtechnologies.com/f...subforms1.html
and
http://www.datapigtechnologies.com/f.../subform2.html

Alan

[kris335]

I tried your expression and it still gave me an error: You have entered an operand without an operator.

And it's interesting you said to use a subform, because that is what I had initally done and I thought it would work perfectly. However, when I went to create a new Family/Parent record, it maintained the children from the previous record. Granted, it added to them since it was in datasheet view within the subform, but I need to be able to glance at the Parent Form and see how many kids they have and what their names are.

This is for our county Christmas program where I have to keep track of Parents (names, contact info, names of kids); Children (names, age, sizes, wish list) and Businesses (contact info, who they are sponsoring.) So I have 3 tables (Parent, Child, Business). I only have one form based on the Parent and I only need to be able to view that onscreen. I have several forms based on the Child Table depending on what I need printed out. the child and parent forms are linked by a Family-App ID.

[kris335]

And I'm putting this expression in Control Source, but I don't think that's the right place. Where should it go for an unbound box? Thanks all for any ideas!

[kris335]

Alan,

Thanks for the links you posted. I was able to get my original subform to work and didn't need to do the unbound box and IIF expression.

Thanks so much for your help. Our county's needy children thanks you too!

Kristy

[alansidman]

Kristy;
Happy you were able to get it to work.

Alan