calculated fields

[DJ100]

Hi,

I have a text box which has a calulated value dependent upon the value of a drop down list . .. it uses nested IIF statements and works quite well in most cases.

However, I have a problem in that the returned value is a combination of other text boxes on the form and if one of them doesn't have an entry in it the formula doesn't return any value . . . .

The part of the code I have a problem with is below ...

Any suggestions would be appreciated

Many thanks

David Jenkins


IIf([BillingDetails]="Customer",[Title]+" "+[Initial(s)]+" "+[Surname]

 

[Fizzio]

Which bit of it is the problem, the [BillingDetails] or the client info bit. If it is the client info bit, you can simply use the Nz Funcion eg Nz([Title],"")
If it is the BillingDetails then you will probably need to filter this before the iif can kick in - especially if you use this as a controlsource of another textbox.

 

[DJ100]

Thanks - will give it a go . . and let you know!

 

[DJ100]

Doesn't work I'm afraid . . .

There are 4 options in the drop down box and dependent upon which you choose the IIf statement does different things.

The problem is only with the client details bit. What I would like it to do is return a space if either [Title] or [Initial] are null.

What it does at the moment is if any of the three fields are null the calculation returns a null value which is a problem . . .

Hope I've explained myself better this time!

 

[Fizzio]

Try this,

IIf([BillingDetails]="Customer",Nz([Title]," ") & " " & Nz([Initial(s)]," ") & " " & [Surname],"No Billing Details)

 

[DJ100]

thank you . . . works like a dream