Name Error in Form

[Tyrant007]

I have a form that is supposed to go to a table and bring in the value of a cost IF the study and visit number match. This doesn't seem that difficult, but I keep getting a name error. If there is a problem with my code below, please let me know. I may just try running a query if all else fails. Thanks alot! - Matt -


=IIf(([Study]=[Procedure_Cost_Per_Subject_per_Visit]![Study] And [Procedure_Cost_Per_Subject_per_Visit]![Visit #]=1),[Procedure_Cost_Per_Subject_per_Visit]![1 budgeted cost],"0.00&quot

 

[SteveR77]

Matt,

Try putting a few more parens in your formula -

=IIf(([Study]=[Procedure_Cost_Per_Subject_per_Visit]![Study]) And ([Procedure_Cost_Per_Subject_per_Visit]![Visit #]=1),[Procedure_Cost_Per_Subject_per_Visit]![1 budgeted cost],"0.00&quot))


HTH,

Steve

 

[ancb]

Are you putting your 'IIF' line in the record source of the text box or is it an expression in your recordset?

 

[Tyrant007]

Its in the control source of the text box.

 

[ancb]

I had this same problem. I had to invode the query builder for the record source of the form and create an expression using the exact code that I had in the controlsource of the text box. Then assigned the expression field to that text box.

 

[Tyrant007]

I don't have a query, im just pulling it from a table. If I did the if/then from the query does it work better? I made my statement in the expression builder, so im sure the names are right. I may end up calling Microsoft today. I've looked on technet and havent found anything that can help. Thanks for looking at this.

 

[ancb]

I had the a very similar situation to what you described. Sometime it worked fine but some times the text box said #Name. I put the IIF statement in the query and haven't had a problem since (about 3 weeks).

 

[Tyrant007]

Thanks for the idea. I put my statement in the criteria of the box I want to update and the query gets no errors!!! The only problem now is it just pulls the null values from the blank table that I want to update. Ive tried an update and append query and it does the same thing. Where did you put your IIF statement in the query?

 

[ancb]

I put it in where the fields go. I just used the build expression wizard. Try using the criteria area to filter the null ie:

<> Null