Syntax error in an unbound field

[Ran BD]

guys I'm trying to calculate a future date for the end of a lease term, I'm
using 2 parameters that I'm retrieving from the form Lease_Start_Date and
Lease_Period.
as I'm using the dateadd function
(=DateAdd("m",[lease_period],[lease_priod_start_Date]) I'm getting the
appropriate result.
trying to eliminate the null (as default value is =0 before data is entered
into the appropriate fields) I'm using
=IIf(IsNull([Lease_Period]),Null,DateAdd("m",[
Lease_Period],[Lease_Period_Start_Date]))

and I am getting #name? in the text box.

what am I doing wrong.



I've been trying to solve this for the last 2 days and I can't figure out
what's going on in here.



thanks



Ran

 

[Dennis Schmidt]

Hi Ran,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

The IIF() function is going to choke on the null value of the field. You
would need to do this with a function. If you use some code similar to the
following on one of your events (control or form), it should do the
calculation for you:

Dim TheDate As Date, TheLength As Variant
TheDate = Lease_Start_Date
TheLength = Lease_Period
If IsNull(TheLength) Then
Text4 = Null
Else
Text4 = DateAdd("m", TheLength, TheDate)
End If


I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support

 

[Ran BD]

Dennis thanks for your advice.
one thing is unclear while the =iff statement was located as the control
source for the unbound text box, where do I insert the code ?

thanks

Ran