Hi

On a detail form with info about a company, there is a subform which state alle other companies that is related to this company, either as mother of child relationship, company - related companies: n:m, cos a company can be child of mother to another company.
In the SQL of the subform, it displays the name of the related company but checks (iif statement) if the related company is child of mother (IIf(mother_id = company_id on form; then "show as mother";else "show as child")

when I archive this company, I want to display all companies that are in the current table and in the archive table. I am wondering if I try to do this in SQL
or in VB (retrieve records, check if related company is in archive table or active table, than display as mother or child)
Anyone?

What flavor of vb are we talking and what library/namespace are you using for data access?

Hi,

I work in access 2007, VB 6. The current code I am using now is like this:
SELECT AR_TBL_hoofd_sub_instellingen.hoofd_id, AR_TBL_hoofd_sub_instellingen.sub_id, IIf([hoofd_id]=[Forms]![arch_frm_instellingen_detail]![instelling_id],IIf(IsNull([ar_tbl_instellingen_1].[naam]),[tbl_instellingen_1].[naam],[ar_tbl_instellingen_1].[naam]),IIf(IsNull([ar_tbl_instellingen].[naam]),[tbl_instellingen].[naam],[ar_tbl_instellingen].[naam])) AS instelling, IIf([hoofd_id]=[Forms]![arch_frm_instellingen_detail]![instelling_id],IIf(IsNull([ar_tbl_instellingen_1].[plaats_postadres]),[tbl_instellingen_1].[plaats_postadres],[ar_tbl_instellingen_1].[plaats_postadres]),IIf(IsNull([ar_tbl_instellingen].[plaats_postadres]),[tbl_instellingen].[plaats_postadres],[ar_tbl_instellingen].[plaats_postadres])) AS plaatsnaam, IIf([hoofd_id]=[Forms]![arch_frm_instellingen_detail]![instelling_id],"neven","hoofd") AS hoofdneven FROM (((AR_TBL_hoofd_sub_instellingen LEFT JOIN AR_TBL_instellingen ON AR_TBL_hoofd_sub_instellingen.hoofd_id=AR_TBL_inst ellingen.instelling_id) LEFT JOIN TBL_instellingen ON AR_TBL_hoofd_sub_instellingen.hoofd_id=TBL_instell ingen.instelling_id) LEFT JOIN AR_TBL_instellingen AS AR_TBL_instellingen_1 ON AR_TBL_hoofd_sub_instellingen.sub_id=AR_TBL_instel lingen_1.instelling_id) LEFT JOIN TBL_instellingen AS TBL_instellingen_1 ON AR_TBL_hoofd_sub_instellingen.sub_id=TBL_instellin gen_1.instelling_id WHERE (((AR_TBL_hoofd_sub_instellingen.hoofd_id)=Forms!a rch_frm_instellingen_detail!instelling_id)) Or (((AR_TBL_hoofd_sub_instellingen.sub_id)=Forms!arc h_frm_instellingen_detail!instelling_id));


this: Forms]![arch_frm_instellingen_detail]![instelling_id is the ID it is getting from the company form. (instelling = company)
hoofd_sub_instellingen is the n:m table that connects 2 different companies. The result are 3 fields: naam (name of company), plaats_postadres (place ) and 'hoofd/neven' (head of sub company)
I am wondering if there is a way around the iif- statement in the SQL query.
Anyone?

I'll be honest with you, I'm having trouble understanding exactly what your iif() statement is trying to do. It looks a bit like you're checking to see if you get a result from the tables you did a LEFT JOIN with. Is this the case? If so, you can take advantage of the fact that you will receive NULL values when the table on the right hand side of a LEFT JOIN statement does not have a matching record. You can use the nz() function like so:

nz([FieldFromRightHandSide], [FieldToUseIfRightSideWasNull]) As [MyField]

Hi Teddy,

I am going to try your suggestion, thanx for the reply.