IIF statement in control of form

[chromachem]

OK. Admittedly, I'm a noob and a goob.
When I enter the code below into the control for txtPrimary on a form, the field shows the #Name? error.

Client and PWSName exist in tblCustody
CertNum exists in tblClient
I can use just CertNum from tblClient on the form. I've tried for a while and give up. the IIF statement must have an error I can't find.

All the tables are available to use in the form.

The form should display either "M-MA072" (our cert number) or the cert number of the lab that sent the sample - tblClient.CertNum

Spelling is correct


[CODE]=IIf([tblCustody]![Client]=[tblCustody]![PWSName],]="M-MA072",[tblClient]![CertNum])[CODE]

Any help is greatly appreciated.

Thanks for looking.

[Micron]

Advise you to use code tags (# button on posting toolbar) because this forum will inject spaces and maybe other unwanted stuff into long expressions. At first glance, you have an extra ending bracket.
[PWSName],]
#Name error usually results from a reference that cannot be resolved. Sometimes it is due to misspelled names.
EDIT - have to admit I'm getting rusty. Not sure if you can use IIF as a controlsource property.
#2 - yes you can. AFAIK, the field used in the expression must be part of the form recordsource. I can't tell if you're trying to reference a different domain (table or query) aside from the one used by the form. If different, you may need to use DLookup with criteria instead/as well. Or get the value via code.

[chromachem]

First off - sorry for not getting the code tags correctly. Second off - typos are corrected

=IIf([tblCustody]![Client]=[tblCustody]![PWSName],"M-MA072",[tblClient]![CertNum]) 

I corrected the typos from the first posting.

One question: Are you allowed to put an IIF in a forms control source field?

Thanks.

[Welshgasman]

Yes

=IIf([Idor]=1,"One","Not one")

[Micron]

One question: Are you allowed to put an IIF in a forms control source field?

Perhaps you read my post before I edited it:

EDIT - have to admit I'm getting rusty. Not sure if you can use IIF as a controlsource property.
#2 - yes you can. AFAIK, the field used in the expression must be part of the form recordsource.

I don't think you can refer to tables and fields that are not part of the form recordsource. You haven't cleared that up yet.

I can't tell if you're trying to reference a different domain (table or query) aside from the one used by the form.

[chromachem]

OK.

You can use an IIF statment in a form control. For example, the following snippet works just fine.

=IIf(1=1,1,2)

Now I'm still stuck with the original issue.

Is there a syntax issue in this line?

=IIf([tblCustody]![Client]=[tblCustody]![PWSName],"M-MA072",[tblClient]![CertNum]) 

[Micron]

I don't think you can refer to tables and fields that are not part of the form recordsource. You haven't cleared that up yet.

Asked twice and still not answered. Good luck, but I'm going to unwatch this one.